SUMIF with text portions

  • Thread starter Thread starter Valerie
  • Start date Start date
V

Valerie

I have a column that shows the state and city (i.e. CO,
Denver) for multiple states and cities. I want to sum for
each state regardless the number of cities without having
to add another column to my worksheet.

LEFT(Location,2) would pull the state but I can't get it
incorporated into my SUMIF formula and work.

Thanks for the help.
 
Say the values you want to sum are in B1:B100 and the city/state info is in
A1:A100

=SUMPRODUCT(--(LEFT(A1:A100,2)="CO"),B1:B100)
 
SUMIF is not a very robust function and is usually
reserved for the easiest of "if-this-sum-this" scenarios.

Try:

=SUMPRODUCT((LEFT(A1:A20,2)="CO")*B1:B20)

HTH
Jason
Atlanta, GA
 
Thanks, Jason! This worked beautifully and I can use this
so much elsewhere!

Valerie
Fort Worth, TX
 
Thanks, guys! For future reference, I got it to work
using a * instead of comma before "B1:B100" - didn't work
using the comma.

This is great!
Valerie
 
Hi
shopuld also work with the comma if you include the '--' in front of
the first operator
 
SUMIF is not a very robust function and is usually
reserved for the easiest of "if-this-sum-this" scenarios.

Try:

=SUMPRODUCT((LEFT(A1:A20,2)="CO")*B1:B20)
Click to expand...
...

Perhaps SUMIF isn't very capable, but

=SUMIF(A1:A20,"CO*",B1:B20)

returns the same result as your formula.
 
Oops! Thought that was a typo - sorry. Works like a
charm! Now I'm curious - what does the "--" tell the
formula? (what is it's purpose in the formula?)

Thanks!
 
It converts a TRUE/FALSE statement (a boolean value) into a number (1*
false=0, 1 * true = 1) which can then be multiplied by the values in B1:B20,
and then those are summed together. It's multiplying by 1 and will return
either 1 or 0.

E.g. if for A1, the first 2 letters are KY, the first part of that formula
would be FALSE (since it's not CO). 1*FALSE = 0, 0*B1=0, and it's the 0
(nothing) added to the sum of the other products (some of which are true).
That's how you get your count.
 
Back
Top