Sumif and And formula
 

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(B110,F11=$DW$11,K11=$DW$12),B 11:B200)

Sumif and And formula
 

That's not the way Sumif works. Read Help for more information. You want a
combination of Sum and If, as in:

=if(and(b110,f11=$dw$11,k11=$dw$12),sum(b11:b200) ,"conditions not met")

--
Regards,
Fred


"StephenAccountant" wrote in
message ...
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(B110,F11=$DW$11,K11=$DW$12),B 11:B200)

Sumif and And formula
 

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.


"Fred Smith" wrote:

That's not the way Sumif works. Read Help for more information. You want a
combination of Sum and If, as in:

=if(and(b110,f11=$dw$11,k11=$dw$12),sum(b11:b200) ,"conditions not met")

--
Regards,
Fred


"StephenAccountant" wrote in
message ...
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(B110,F11=$DW$11,K11=$DW$12),B 11:B200)

Sumif and And formula
 

Try this:

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

Biff

"StephenAccountant" wrote in
message ...
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(B110,F11=$DW$11,K11=$DW$12),B 11:B200)

Sumif and And formula
 

Yep, that worked.

Thanks guys.

"Biff" wrote:

Try this:

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

Biff

"StephenAccountant" wrote in
message ...
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(B110,F11=$DW$11,K11=$DW$12),B 11:B200)

Sumif and And formula
 

You're welcome. Thanks for the feedback!

Biff

"StephenAccountant" wrote in
message ...
Yep, that worked.

Thanks guys.

"Biff" wrote:

Try this:

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

Biff

"StephenAccountant" wrote
in
message ...
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(B110,F11=$DW$11,K11=$DW$12),B 11:B200)