Need Something Easier than a PivotTable, but Harder than VLookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TKrepitch@aol.com - 22 Jan 2007 21:33 GMT

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!

Reply to this Message

aaron.kempf@gmail.com - 22 Jan 2007 21:38 GMT

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

it's drag and drop!

lose the f.cking 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

Reply to this Message

TKrepitch@aol.com - 22 Jan 2007 21:43 GMT

> im sorry.. but how in the hell are pivot tables TOO COMPLEX?
>
[quoted text clipped - 7 lines]
>
> -Aaron

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.

Reply to this Message

aaron.kempf@gmail.com - 22 Jan 2007 22:34 GMT

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

> > im sorry.. but how in the hell are pivot tables TOO COMPLEX?
> >
[quoted text clipped - 12 lines]
> If I can write a formula, I won't have to refresh the pivot table
> everytime something changes.

Reply to this Message

TKrepitch@aol.com - 22 Jan 2007 22:49 GMT

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.

Reply to this Message

Martin Fishlock - 23 Jan 2007 01:42 GMT

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.

Signature

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

> 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
[quoted text clipped - 16 lines]
> 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.

Reply to this Message