Adding up all Cells That Meet Criteria

[fx101]

I've searched far and wide and cannot figure out how to do this...

I have a workbook with two worksheets. The first worksheet looks something like:

Local ID---Town Name---Aggregate Property Values---Regional Aggregate Property Values
1 ...... New York ........ $100,000,000,000.................$105,000,000,000
2 ...... Scarsdale .........$5,000,000,000....................(empty)
7......
145.... Lima ...........$25,000,000,000........................$25,000,000,000
(etc)

With the regional aggregate property values being the column i want to find a way to get the values for.

The second worksheet looks like...

Local ID --- Town Name --- Regional ID
1 .........New York ................1
2 ........ Scarsdale ...............1
(blank)....(TOTALS)..............1
7...........Lima......................7
(blank)....(TOTALS)..............1


So what I want to do is find the sum of the Aggregate Property Values for all towns with the same regional ID and place them under the first entry listed for that region.

Let me give you a practical example with my simplified sheets...
I would like to add up the aggregate property values of New York and Scarsdale (because they both have the same regional ID), and I want to put the sum of their aggregate values in New York's row (because its regional ID=1=Local ID). For Lima, since it has no other towns that lie in its region, I would just like to have it transfer over to the Regional Aggregate Property Value Column since there's nothing to add up.

How would I go about this? I have 1500 rows so I can't do it by hand.....

[tigeravatar]

fx101,

Welcome to the forum. See attached. I inserted a helper column in Sheet1 (column B) that uses a VLOOKUP formula to get the Regional ID. Then in Sheet1 column E, the "Regional Aggregate Property Values", I entered the following formula:
=IF(B2="","",IF(COUNTIF($B$1:B2,B2)>1,"",SUMIF(B:B,B2,D:D)))

Then I copied down. Is something like that what you're looking for?

Hope that helps
~tigeravatar