SUM cells with equal values in an adjacent column.

I've attached an image that illustrates what I need to do.

I need to sum the population of each county (from multiple cells) and each city (from multiple cells), only if there are multiple counties and/ or cities.

excel-reference-sums.png

As you can see, Autauga has multiple cities and multiple zip codes. Prattville has multiple zip codes. I want to total the county population, based on the values included in each zip code, and the city populations based on the same criteria.

If this isn't possible, I may cry. The work I need to do, based on this example is ultra-extensive. I'm looking for shortcuts.

Use this in F2:

Formula:

Please Login or Register  to view this content.

And use this in G2:

Formula:

Please Login or Register  to view this content.

And extend formula down

Thank you, zbor!

Do you know if this formula works in OpenOffice Calc?

It doesn't seem to.

most common reason being that OpenOffice.org uses semi-colons ( ; ) between arguments, instead of commas ( , ) like in Excel.

F2:

Formula:

Please Login or Register  to view this content.

G2:

Formula:

Please Login or Register  to view this content.

Using the updated formulas produces a "#NAME?" error.

Both of them?

try replace them with:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Extend range (1000) if needed

Here's an example, with some data.

http://uscomputerrepair.net/wp-conte...um-example.xls

BOOM! That worked!

Can you explain what you mean by "Extend range (1000) if needed"?

You have quoatation around ranges. Remove them and it will work.

Also, 2nd formula (in G2) is different than 1st. (See above).

This seems to work just the way I need it to. I'm freakin' overjoyed!

Originally Posted by zbor

Both of them?

try replace them with:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Extend range (1000) if needed

I really don't know. They weren't in the file I originally saved.

Originally Posted by martindwilson

=IF(COUNTIF(C$2:C2,C2)=1,SUMIF("c":"c",C2,"e":"e"),"") what is this? where did those quotes come from?
=IF(COUNTIF(C$2:C2,C2)=1,SUMIF(c:c,C2,e:e),"")
and while im here open office doesnt support countifs so zbor's sumproduct() is the way to do it