Thanks:  0
Likes:  0

# Thread: SumIf with multiple criteria?

1. 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

=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

3. 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?

4. 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.

5. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•