# Sum with multiple different criteria

#### gorillawar

##### New Member
Hello,

I have one range I am trying to count the occurrence of aword, based on two other rows criteria. Probably better explain with an example.
A1:A5 contains the word “Closed” or “Open” I want to counthow many time the word “Open” is present, based off of the two following rowsbeing true.
B1:B5 contain dates ranging across different months. I wantto only count the “Open” words if the date is the first 6 months in the year.
C1:C5 is a counting formula the is related to a few otherthings, but I also want it to only count those “Open” words if this number isgreater than, or equal to 1.

I have tried to do this myself, but I assume it iscompletely wrong
=COUNTIF((A1:A5,"Open")*(MONTH(B1:B5)>=6)*(C1:C5>=1))

Any help is greatly appreciated!

Thanks,

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Try
Code:
``=SUMPRODUCT((A1:A5="open")*(MONTH(B1:B5)<=6)*(C1:C5>=1))``

Thank you that was it. I am not sure why I didn't try SUMPRODUCT instead of COUNTIF.. Guess I still have a lot more to learn!

Ok never mind, I just tried to change "open" to "closed" and it is just pulling all of the words. It is not filtering the ">=1" from my third criteria I listed initially.

Could you post an example of what is happening.

I am using the following formula:

=SUMPRODUCT((Table14[Open/ Closed]="closed")*(MONTH(Table14[Initiated])<=6)*(Table14[Days Past Due]>=1))

I changed the word "open" with "closed" and it is counting all references of the word "closed", when it should only count 7, since I only have 7 rows that are closed, and also have "days past due" that is >=1

If you go into edit mode (F2) and select the green part and press F9 you should get a series of true and false. Does this return the correct true or false for each row?
Code:
``=SUMPRODUCT((Table14[Open/ Closed]="closed")*(MONTH(Table14[Initiated])<=6)*[COLOR=#00ff00](Table14[Days Past Due]>=1)[/COLOR])``

No it is not, all are saying true, when 5 should be false.

Could this be because all of those cells are numbers that are from a formula? I put the formula I use below.

=IF(ISBLANK(D11),"-",IF(D11>TODAY(),"-",IF(D11=E11,"-",IF(E11="",TODAY()-D11,IF(D11>E11,"-",E11-D11)))))

This basically just leaves the cell with a - s there s not info to use. Otherwise it will count days, which is the number im using. I just want it to count the cell that are 1 or above, all of the - ones I don't want counted.

Try
Code:
``=SUMPRODUCT((Table1[Open/ Closed]="Closed")*(MONTH(Table1[Initiated])<=6)*(Table1[Days Past Due]>=1)*(Table1[Days Past Due]<>"-"))``

Replies
3
Views
137
Replies
7
Views
538
Replies
1
Views
223
Replies
4
Views
366
Replies
9
Views
351

1,202,902
Messages
6,052,456
Members
444,584
Latest member
gsupike

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

### Which adblocker are you using?

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

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