SUMIF relative calcutions?

[Jeff Polack]

I'm trying to use a SUMIF formula to total the values of
each row which is selected because the search criteria
matches an entry in one of several columns:

=SUMIF(A1:G10,X,$H$1:$H$10)

In other words, if the function finds X in B5 it will add
the value in H5 to the total. Same thing if the function
finds X on C5 or D5, etc.

This works fine if X is found in column A. However, if X
is found in column B, SUMIF uses the value found in column
I (i.e., a relative calculation, 1 to the right of H
because B is one to the right of A). This occurs even if
the sum_range is locked so as not to be relative (i.e., as
shown above).

Anyone have any ideas on how to work around this w/o
structuring a SUMIF on each column (e.g., A-G)?

Thanks in advance!

 

[Vasant Nanavati]

I don't believe that SUMIF is designed to work in this manner. How would it
handle multiple Xs on the same row?

 

[Peo Sjoblom]

Maybe this will do?

=SUMPRODUCT((A1:G10=X)*(H1:H10))

 

[Guest]

Hi Group
I get a count, when I use this formula. Tried a couple of others and get counts too. One counts the Xs, if I have ones in the sum column = 70, but if I put a 2 in that column, I get 77. Don't see a way, but that doesn mean there isn't isn't in Ecel. I suprises several times a week at least.

 

[Frank Kabel]

Hi Jeff
one workaround:
=SUMPRODUCT(--((A1:A10="x")+(B1:B10="x")+(..)+(G1:G10="x")>0),H1:H10)

 

[Leo Heuser]

Jeff

One way:

=SUM(MMULT((A1:G10=X)+0,TRANSPOSE((A1:G1=A1:G1)+0))*H1:H10)

 

[Leo Heuser]

Sorry, forgot to mention, that the formula
is an array formula and therefor must be
entered with <Shift><Ctrl><Enter>, also
if edited later.

LeoH

..

 

[Jeff Polack]

Thanks to everyone who took the time to reply.