Need Something Easier than a PivotTable, but Harder than VLookup

T

TKrepitch

I wasn't able to find a solution in this group, but it may be because
I'm not quite sure how to search for it.

Here's the issue:

In cells E9:E58, I have input cells for the month. In cells F9:F58, I
have input cells for a dollar amount. The key here is that any of
these numbers can repeat (e.g., E12 could be October 07 and so could
E39).

Cells H8:IM8 list the months chronologically and do not repeat.

What I want to do is sum the dollar values and put them in cells
H59:IM59, based on the month that is in the range H8:IM8.

For example, in cell H8, I have April 04. I want to write a formula in
cell H59 that tells Excel to look at what is in H8, then find each
occurrence of it in E9:E58 and give me the sum of each adjacent cell in
F9:F58.

Normally I would use a lookup function if there was only one possible
occurrence of the month, but I don't know what to do since there can be
multiple occurrences. I could probably use a pivot table, but I'd
rather use a formula. I'm thinking something like sumif, but I don't
know if I can use that. I'd appreciate any advice. Thanks!
 
A

aaron.kempf

im sorry.. but how in the hell are pivot tables TOO COMPLEX?

it's drag and drop!

lose the fucking training wheels; do you like being handicapped as a
1st grader?

go and learn a database program and a reporting program and uninstall
Excel

-Aaron
 
T

TKrepitch

im sorry.. but how in the hell are pivot tables TOO COMPLEX?

it's drag and drop!

lose the fucking training wheels; do you like being handicapped as a
1st grader?

go and learn a database program and a reporting program and uninstall
Excel

-Aaron
Click to expand...

Geez...all I meant was I looking for something more elegant.

If I can write a formula, I won't have to refresh the pivot table
everytime something changes.
 
A

aaron.kempf

what are you hooking your pivot table into?

I think that pivotTables against Analysis Services have got to be the
most powerful thing in the world

-Aaron
 
T

TKrepitch

Well, I'm trying to avoid using a PivotTable if I can. I know a
PivotTable would work, but I'd like to be able to write a formula
(something similar to SUMIF, DSUM, or something like that). This is
what I am looking at:

Month Dollars
Apr-04 500
Jul-04 100
Oct-04 400
Oct-07 700
Apr-04 300

What I want is a formula that will look through that table and tell me
how many dollars were spent in Apr-04. Some of the functions I've
tried look close, but there is always something preventing it from
working.

I've seen some ingenious solutions on this site, so I was hoping
someone would have a trick or know of a formula I don't. Otherwise,
I'll put in a pivot table, but I'd prefer the auto-updating formula.
 
T

TKrepitch

Beautiful! There's one of those solutions I was looking for! Thanks!

I think I just need to tweak it a bit so I can reference the month in
another cell, but this was the hard part. Much appreciated! Thanks
again!
 
M

Martin Fishlock

Hi,

The question for the test is how is the date formated. So one of the easiest
ways is to convert the text date to a string and compare that.

=SUMPRODUCT(--(TEXT(A2:A6,"Mmm-yy")="Apr-04"),B2:B6)

This has problems in none english versions as Mmm is not always Apr forthe
fourth month.
 
R

Ron Coderre

Thanks for the feedback....I'm glad you could work with that.


***********
Regards,
Ron

XL2002, WinXP
 
P

Pritcham

Hi

I know a solution has already been posted here but is there any reason
why you're not just using a straight SUMIF()?

To test this I entered your sample data (posted earlier) in cells A1:B6
(including a header - as shown below), and summarised the data in D1:H2
(again, shown below) with a straight SUMIF() - worked no problem.

Cells A1:B6
Month/Year Amount
Apr-04 500
Jul-04 100
Oct-04 400
Oct-07 700
Apr-04 300

Cells D1:H2
Month/Year Apr-04 May-04 Jun-04 Jul-04
Amount 800 0 0 100

The formula in E2:H2 cells is:
=SUMIF($A$2:$A$6,E1,$B$2:$B$6)

As I say, I know an alternative solution has been posted but thought
this might help as well.

Cheers
Martin
 
T

TKrepitch

You are totally correct...I don't know what I was doing wrong when I
tried SUMIF earlier. D'oh! Thanks!
 
T

TKrepitch

Thanks everyone for your help. I got multiple solutions to my problem
and learned some cool new tips. I appreciate it!

:)
 
Top