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

Try this:

=SUMPRODUCT(--(R9:R48=A6),M9:M48)

HTH
Elkar


"Jeannine" wrote:

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

Try this:

=SUMIF(R9:R12,A6,M9:M12)

--
Biff
Microsoft Excel MVP


"Jeannine" wrote in message
...
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?

"Elkar" wrote:

Try this:

=SUMPRODUCT(--(R9:R48=A6),M9:M48)

HTH
Elkar


"Jeannine" wrote:

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

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


"Jeannine" wrote:

Thank you so much Elkar - It worked perfectly, I have never seen two dashes,
what does that mean?

"Elkar" wrote:

Try this:

=SUMPRODUCT(--(R9:R48=A6),M9:M48)

HTH
Elkar


"Jeannine" wrote:

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

I am surprized but I actually understand that - thanks so much you are making
look smart.

"Elkar" wrote:

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


"Jeannine" wrote:

Thank you so much Elkar - It worked perfectly, I have never seen two dashes,
what does that mean?

"Elkar" wrote:

Try this:

=SUMPRODUCT(--(R9:R48=A6),M9:M48)

HTH
Elkar


"Jeannine" wrote:

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