Sum with multiple different criteria

[gorillawar]

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,

 

[Scott T]

Try

=SUMPRODUCT((A1:A5="open")*(MONTH(B1:B5)<=6)*(C1:C5>=1))

 

[gorillawar]

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!

 

[gorillawar]

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.

 

[Scott T]

Could you post an example of what is happening.

 

[gorillawar]

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

 

[Scott T]

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?

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

 

[gorillawar]

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

 

[gorillawar]

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.

 

[Scott T]

Try

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