SumIf with multiple criteria?

mdecourcy · Mar 3rd, 2002, 07:09 PM

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

Mar 3rd, 2002, 10:24 PM

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 · Mar 4th, 2002, 12:43 AM

Quote:

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 · Mar 4th, 2002, 08:43 AM

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 · Mar 4th, 2002, 09:02 AM

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 3rd, 2002, 07:09 PM	#1
mdecourcy New Member Join Date: Mar 2002 Posts: 2	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

Mar 3rd, 2002, 10:24 PM	#2
Guest Posts: n/a	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

Mar 4th, 2002, 08:43 AM	#4
mdecourcy New Member Join Date: Mar 2002 Posts: 2	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.