SUMPRODUCT Question 3

[NumberCrunchingMonky]

I'm using SUMPRODUCT and need to exclude a particular variable from my SUMPRODUCT criteria. How do I do this?

For example, I have a table of all cities(col.A) in all states(col.B) in the US. There's also a field with the population of each state(col.C).

If I wanted to Sum the population of all cities in the state of California I would do something like this (given there are named ranges for "City", "State", "Population&quot.

=SUMPRODUCT((State="California&quot*(Population))

But what if I wanted to sum the population of the state of California, minus, say, San Francisco? Being there are too many cities in the state to specify in the formula individually I need to exclde "San Francisco" another way formulaically.

So, the formula couldn't look like this:
=SUMPRODUCT(((City="Alameida&quot+(City="Sacramento&quot+(City="Los Angeles&quot+(etc.)+(etc.))*(State="California&quot*(Population))

How do I do this?

Thank you.

Monky

 

[Zathras]

Something like

  =SUMPRODUCT("state of CA") - SUMPRODUCT("San Francisco")

But I would probably use DSUM in a case like this.

 

[KenWright]

=SUMPRODUCT((State=&quot;California&quot*(City<>&quot;San Francisco&quot*(Population))

Regards
Ken................


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[jfhewitt]

I have a worksheet using SUMPRODUCT that works fine with the array defined as cell ranges, but when I name the array as a range, it won't work. Any ideas?

 

[xlbo]

the named range should NOT include the headers and shouldn't be the whole column. If your ranges include either of these, they need re-defining

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[jfhewitt]

Thanks, xlbo
No...the range does not include a heading nor is it the full column...it does include some blank cells at the bottom of the range.

 

[KenWright]

The ranges MUST be exactly the same length.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[NumberCrunchingMonky]

Ken
Zathras

Your suggestions both work very well. Many thanks for your help. And I'll check out DSUM in future situations.

Thanks,

NCM

 

[NumberCrunchingMonky]

Thanks for the tip xlbo
NCM