SUMIF partial string criteria
 

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

SUMIF partial string criteria
 

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" wrote in message
...
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

SUMIF partial string criteria
 

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

On Sat, 21 Mar 2009 10:56:08 -0400, "Ron Coderre"
wrote:

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" wrote in message
.. .
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

SUMIF partial string criteria
 

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

On Sat, 21 Mar 2009 10:56:08 -0400, "Ron Coderre"
wrote:

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" wrote in message
.. .
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

SUMIF partial string criteria
 

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.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Fred Holmes" wrote:

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

On Sat, 21 Mar 2009 10:56:08 -0400, "Ron Coderre"
wrote:

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" wrote in message
.. .
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