SUMIF criterion to match the first 5 characters.
 

Excel 2000

How do I write a SUMIF function, such that the matching text criterion
matches the first five characters of the string in the cells of the
range to be evaluated for a match? Something like the following:

=SUMIF(LEFT(A5:A1000,5),"Total",C5:C1000)

Many thanks,

Fred Holmes

Answer: SUMIF criterion to match the first 5 characters.
 

Hi Fred,

To match the first five characters of a string in a SUMIF function, you can use the LEFT function within the SUMIF function. Here's how you can write the formula:

1. =SUMIF(LEFT(A5:A1000,5),"Total"&"*",C5:C1000)

In this formula, the LEFT function extracts the first five characters from each cell in the range A5:A1000. The &"*" is used to add a wildcard character to the end of the "Total" string, so that any text that starts with "Total" will be included in the sum. Finally, the range C5:C1000 contains the values that will be summed.

SUMIF criterion to match the first 5 characters.
 

One suggestion

=SUMPRODUCT(--(LEFT(A5:A1000,5)="Total"),C5:C1000)

"Fred Holmes" wrote:

Excel 2000

How do I write a SUMIF function, such that the matching text criterion
matches the first five characters of the string in the cells of the
range to be evaluated for a match? Something like the following:

=SUMIF(LEFT(A5:A1000,5),"Total",C5:C1000)

Many thanks,

Fred Holmes

SUMIF criterion to match the first 5 characters.
 

Try this:

=SUMIF(A5:A1000,"Total*",C5:C1000)


"Fred Holmes" wrote:

Excel 2000

How do I write a SUMIF function, such that the matching text criterion
matches the first five characters of the string in the cells of the
range to be evaluated for a match? Something like the following:

=SUMIF(LEFT(A5:A1000,5),"Total",C5:C1000)

Many thanks,

Fred Holmes