summing table values with multiple criteria from a dynamic table

**griffith** · 05-05-2005, 03:23 PM

Hi,

I am trying to build formulas that will lookup values in a table. The problem is the table is dynamic, i.e. it will change with user input. I want to convert information in this dynamic table to a fixed location. The dynamic table displays year placed in service (YrInService), month placed in service (MoInService) and dollar amount for that month and year (UnfinancedPortion). An example entry for row1 might be
YrInservice = 2
MoInService = 4
Unfinancied = $100000

row 2 might be

YrInservice = 1
MoInService = 6
Unfinancied = $25000

row 3 might be

YrInservice = 3
MoInService = 3
Unfinancied = $250000

up to 12 rows

User input controls the YrInService and MoInService and the table may not be in any kind of order, i.e. ascending.

From this table I want to sum the dollar values for that meet the same year and month criteria (all year 1, month 6 or year 2, month 8) and place them in another table in correct cronological order, i.e. if year is 2 and month is 4 (total of 28 months) it would place the total dollar amount that meet this criteria in the 28th column of the second table.

My problem is that the sample table above is dynamic where YrInService can range from 1 to 3 and MoInService can range from 1 to 12 for any row.

Any help would be greatly appreciated.

**olasa** · 05-05-2005, 03:49 PM

Sounds like a job for a Pivottable that works with a Dynamic Range - See encl. Picture.

Hope this helps
Ola Sandström

Video examples:
Pivottables: http://www.datapigtechnologies.com/f...es/pivot1.html
Dynamic Ranges: http://www.datapigtechnologies.com/f...micranges.html

**griffith** · 05-05-2005, 06:24 PM

Thanks for the quick reply. This gets me closer, but still not quite there, I think.

More information:

You can see that column C and D can have zeros and they can be in any row in the columns C and D, but should be at the bottom most of the time.

The block of Cells G5:N9 is a mockup of the results I would like. If I can get to the results in this mockup format, I can get to the next step which is transfering the years and months into one row that is 36 columns long (3 years, 12 months each) on another tab.

This is my first time using this site so my apologies for not knowing how to run the software a little better. I have attached a jpg this time that will better illustrate the results I need.

**olasa** · 05-06-2005, 09:59 AM

Thanks for the picture.
Here is a sample that I hope is what you want (see enclosed).
I also adjusted the Pivottable so it looks like you picture.

Ola Sandström

Note:
It Involves Array Formulas.
Here is some info on array formulas: http://www.personal-computer-tutor.c...ayformulas.htm
Normally Array formulas must be confirmed by holding down Ctrl+Shift and then hit Enter. So just typing enter after you've written the formula won't do the trick. But this on don't need this.

**griffith** · 05-06-2005, 01:52 PM

Ola,

Thanks for the continued help. Your last reply worked. I ended up using just one sumproduct statement as I preferred the 0 entries.

You can't believe how much time I have spent on this. This was a tremendous help. If you every get to Montana, I owe you supper.

Duane Griffith

**olasa** · 05-06-2005, 05:51 PM

How can I say no to to such a generous offer from Montana. 2 weeks ago I got an invitation to Australia. If I'm lucky, after 25-30 years, I retire with paid meals - I just have to find a way to finance the trips :-).

Happy to hear that it made a difference.

Ola Sandström