Formula Combining the SUMIF and LEFT functions to calculate totals

**The_Snook** · 01-29-2013, 10:18 AM

Hi,

I'm sure there's a simple resolution to this but i'm sick of wasting my time on it. Basically i'm looking to calculate the total of two columns based on whether the data in the first column starts with 40 or 50.

Thanks in advance,

Snook

**benishiryo** · 01-29-2013, 10:27 AM

hi Snook, try:
=SUMPRODUCT((LEFT($B$3:$B$24,2)="40")*($C$3:$C$24))

or change B27 to just 40. then use:
=SUMPRODUCT((LEFT($B$3:$B$24,2)=B27&"")*($C$3:$C$24))

1st formula must change the red text to "50" for B28. 2nd formula works for both

**Norie** · 01-29-2013, 10:29 AM

The values in column B are numbers, so you I don't think you can use wildcards - I could be wrong though.

Anyway, this formula worked for 40,

Formula:

Please Login or Register  to view this content.

and this for 50.

Formula:

Please Login or Register  to view this content.

**The_Snook** · 01-29-2013, 10:34 AM

Thanks, for my own sanity and development where was I going wrong in relation to the formulas I had a bash at in the attached example?

Thanks,

Snook

**benishiryo** · 01-29-2013, 10:50 AM

in your formula for C27, you are basically saying in B3:B24, find the statement "TRUE" & sum up C3:C24. that's because you used your criteria as LEFT(B3,2)="40". that is a logical test. you are saying is 2 characters from the left of B3 equals to "40"? so the answer is TRUE.

if B3:B24 were to be texts, then you could use something like:
=SUMIF($B$3:$B$24,"40*",$C$3:$C$24)
but unfortunately, as nonie mentioned, SUMIF doesnt allow wildcards for numbers

**The_Snook** · 01-29-2013, 01:28 PM

Thanks both, that makes a lot more sense now!

Another problem has now arisen, is it possible to amend the formula to pick up two criteria? I'm working with a much larger data set and need to add the codes "509" and "6".

I've managed to achieve what i'm after by adding a SUM formula at the start and copying the SUMPRODUCT formula and amending it to incorporate the second criteria I require. This strikes me as long winded and i'm there must be a shorter alternative?

Thanks

Snook