Sumif and And formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have to add some numbers in one column based on multiple criteria.

EG,

if cell b11 > 0 and Cell F11 = Cell$DW$11 and cell K11 = Cell $DW$12
Then
Add cells B11:B200.

Keep in mind that the criteria for each row must match.

Can I do this using sumif? I have a formula but it doesn't seem to work:

=sumif(B11:B200,and(B11>0,F11=$DW$11,K11=$DW$12),B11:B200)
 
That's not the way Sumif works. Read Help for more information. You want a
combination of Sum and If, as in:

=if(and(b11>0,f11=$dw$11,k11=$dw$12),sum(b11:b200),"conditions not met")
 
That won't work unfortunately because i have 200 rows.

The criteria for row number 12 must match but for row number 12.

so that means that my cell F11 reference is only for row 11.
 
Try this:

=SUMPRODUCT(--(B11:B200>0),--(F11:F200=$DW$11),--(K11:K200=$DW$12),B11:B200)

Biff
 
Back
Top