Sumif and And formula

[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)

 

[Fred Smith]

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")

 

[Guest]

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.

 

[Biff]

Try this:

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

Biff

 

[Guest]

Yep, that worked.

Thanks guys.

Try this:

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

Biff

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Yep, that worked.

Thanks guys.