SUMIF partial string criteria

[Fred Holmes]

SUMIF(range,criteria,sum_range)

Where string (cell formula) criteria is tested against the string in
the "range". This test is a full string comparison.

How do I write the formula so that the full "criteria" string is
tested against a subset of the string in "range," e.g. LEFT(range,2)?

LEFT(range,2) per se doesn't work.

Basically I'm subtotalling data by accounting classification, which is
a four digit string of ciphers. The SUMIF function works fine for me
to subtotal, e.g., all of the entries for which the Account No. is
"1234". I want to run a separate subtoatl of all of the entries for
which the Account No. begins with "12". And I explictly want to
calculate it in this fashion, not by adding the indvidual subtotals
that have been generated.

TIA

Fred Holmes

 

[Ron Coderre]

If the 4 digit Account No is text...try something like this:
=SUMIF(A1:A10,"12*",B1:B10)

if Account No is either numeric or text:
=SUMPRODUCT((--LEFT(A1:A10,2)=12)*B1:B10)

Is that something you can work with?

Regards,

Ron Coderre
Microsoft MVP (Excel)

 

[Fred Holmes]

Looks good! Many thank! I'll give it a shot.
Fred Holmes

 

[Fred Holmes]

And it even works if the "critera" is a range reference

=FIXED(SUMIF(R4C8:R500C8,RC8&"*",R4C10:R500C10),2)

Forgive the R1C1 notation, but that's what I like to work with.

Many thanks,

Fred Holmes

 

[Shane Devenshire]

Hi,

Do your really need the results returned as text? If not simplify the
formula by removing the FIXED and just apply a format to the cell.

=SUMIF(R4C8:R500C8,RC8&"*",R4C10:R500C10)

Just a comment - R1C1 notation is going to be more of a challenge in Excel
2007 since there are 16,384 coulumn. You will be getting references like
R1046123C15231:R1048123C16123.