SumIf with multiple criteria?

mdecourcy

New Member
Joined
Mar 2, 2002
Messages
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top