We help IT Professionals succeed at work.

What is the syntax to return a text string in an Excel SUMIF statement ?

Tocogroup
Tocogroup asked
on
Hi All,

I am trying to use a SUMIF function in an Excel worksheet to total a number of amounts in a row where the corresponding column name for each amount ends in the text string "Cost".

So, in my example here,

=SUMIF(H2:DG2,RIGHT(H2:DG2,4)="Cost",H4:DG4)

I want to total any amount in range H4:DG4 that has a column heading (H2:DG2) which ends in the text "Cost".

I can't get it to work !

Any ideas ? Thanks for your help.
Toco
Comment
Watch Question

AlanConsultant

Commented:
Hi,

I would do it like this:

=SUMPRODUCT((RIGHT(H2:DG2,4)="Cost")*(H4:DG4))

Hope that helps,

Alan.

Author

Commented:
I'm afraid the data range includes text values which returns me #VALUE errors.
Is there another way of doing this ?
Most Valuable Expert 2011
Top Expert 2011
Commented:
=SUMIF(H2:DG2,"*Cost",H4:DG4)

should do it.

Author

Commented:
And that did do it. Thanks Rory. Great stuff