IF formula

  • Thread starter Thread starter Jeannine
  • Start date Start date
J

Jeannine

I am trying to lookup an exact value (1113) in one cell and one column. then
move across the row to a different colunm to add the values in that column.
If I do as follows:
=IF(A6=R9,M9,0)+IF(A6-R10,M10,0)+IF(A6=R11,M11,0)+IF(A6=R12,M12,0)

It works just fine but if I have 40 rows that is a lot of IF's. Is their a
way to do this in a shorter way. the value is in "A6" AND I am searching
column "R" for an exact match. If I find matches in "R" then I wanted the
value on the same rows in Column "M" added together.

I hope this makes sense - I would appreciate your help - thanks in advance
 
Thank you so much Elkar - It worked perfectly, I have never seen two dashes,
what does that mean?
 
Thank you so much Elkar - It worked perfectly, I have never seen two dashes,
what does that mean?
 
Basically, the -- is just multiplying by -1 twice. SUMPRODUCT needs to work
with numbers. The statement (R9:R48=A6) returns either TRUE or FALSE, not a
number. Excel will treat TRUE as 1 and FALSE as 0 for calculations, but
SUMPRODUCT still doesn't work with that, so by multiplying by -1 twice, you
convert the TRUE/FALSE to a number without changing the value which makes
SUMPRODUCT happy.

HTH
Elkar
 
Basically, the -- is just multiplying by -1 twice. SUMPRODUCT needs to work
with numbers. The statement (R9:R48=A6) returns either TRUE or FALSE, not a
number. Excel will treat TRUE as 1 and FALSE as 0 for calculations, but
SUMPRODUCT still doesn't work with that, so by multiplying by -1 twice, you
convert the TRUE/FALSE to a number without changing the value which makes
SUMPRODUCT happy.

HTH
Elkar
 
Back
Top