SUMIF relative calcutions?

  • Thread starter Thread starter Jeff Polack
  • Start date Start date
J

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!
 
I don't believe that SUMIF is designed to work in this manner. How would it
handle multiple Xs on the same row?
 
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.
 
Hi Jeff
one workaround:
=SUMPRODUCT(--((A1:A10="x")+(B1:B10="x")+(..)+(G1:G10="x")>0),H1:H10)
 
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

..
 
Back
Top