SumIf with multiple criteria?

[mdecourcy]

I want to evaluate a boolean value in one column (B2:B1000), then, if the value is true, evaluate column C (C2:C1000) and if that value is also true, sum D (D2:D1000).

I want to avoid using a conditional as I am using this sheet on my IPaq as well as my PC and you can't use conditionals on a CE device.

TIA

 

[Guest]

I think this might help you out hopefully

=SUM(((b2:b1000=XXX)*(C2:c1000=xxx))*(d2:d2000))

as an array formula (shift alt delete)

fill in the xxx to whatever value or cell reference you want

 

[Aladin Akyurek]

On 2002-03-03 18:09, mdecourcy wrote:
I want to evaluate a boolean value in one column (B2:B1000), then, if the value is true, evaluate column C (C2:C1000) and if that value is also true, sum D (D2:D1000).

I want to avoid using a conditional as I am using this sheet on my IPaq as well as my PC and you can't use conditionals on a CE device.

TIA

In E2 enter: =(B2<$F$1)*(C2=$F$2)*D2

where F1 and F2 houses your conditions.

Adjust the comp operators < and = to suit and copy down this as far as needed.

In F2 enter: =SUM(E:E)

Would this work on CE?

Aladin

 

[mdecourcy]

My mistake, I meant to say I can't use an array formula as THEY don't work on CE.

I have one cell I want to return the total to so if I had a single column to evalute I would have; =sumif(B:B, "y", C:C).

But I want to evalute A based on the results of B, so if B:B is "y" AND A:A is "y" then Sum C:C.

Hope this is clear.

 

[Aladin Akyurek]

On 2002-03-04 07:43, mdecourcy wrote:
My mistake, I meant to say I can't use an array formula as THEY don't work on CE.

I have one cell I want to return the total to so if I had a single column to evalute I would have; =sumif(B:B, "y", C:C).

But I want to evalute A based on the results of B, so if B:B is "y" AND A:A is "y" then Sum C:C.

Hope this is clear.

Try what I proposed. It is not array formula. With your now more specific info, do:

In E2 enter: =(A2="y")*(B2="y")*C2

where I assumed the data to start in row 2, otherwise adjust to suit, and copy down this as far as needed. Then use

=SUM(C:C)

to obtain the desired total.

Aladin