Formula help

  • Thread starter Thread starter mars
  • Start date Start date
M

mars

Can anyone help me with this formula ... (I know there is a way, but
can't think of it now, nor is the MS Help, helping me)...

Problem: I need to know how to correctly word a formula to add togethe
the following cells: L2:L196,R2:R196,U2:U196,V2:V196,Z2:Z196
if J2:J196,J209


I had this originally (but now need to add additional columns),
=SUMIF(J2:J196,J209,L2:L196)

but I need to add this to it:
R2:R196,U2:U196,V2:V196,Z2:Z19
 
mars > said:
Problem: I need to know how to correctly word a formula to add together
the following cells: L2:L196,R2:R196,U2:U196,V2:V196,Z2:Z196
if J2:J196,J209

I had this originally (but now need to add additional columns),
=SUMIF(J2:J196,J209,L2:L196)

but I need to add this to it:
R2:R196,U2:U196,V2:V196,Z2:Z196
Click to expand...

Do you mean you want to apply the same condition to the col R, U, V and Z
ranges? If so, one way would be brute force.

=SUMIF(J2:J196,J209,L2:L196)+SUMIF(J2:J196,J209,R2:R196)
+SUMIF(J2:J196,J209,U2:U196)+SUMIF(J2:J196,J209,V2:V196)
+SUMIF(J2:J196,J209,Z2:Z196)

Somewhat more elegant would be SUMPRODUCT.

=SUMPRODUCT(--(J2:J196<?>J209),L2:L196+R2:R196+U2:U196+V2:V196+Z2:Z196)

where <?>J209 should be replaced by the exact text in J209.
 
Back
Top