Countifs

[JimmieNeu]

This is the formula I am trying to use:

=COUNTIFS(A5,"PQ",C4:C8,"Actual Straight")

It is not working, I am getting a "#VALUE!" error.


If I expand A5 into a range of cells (A4:A8), it sort-of works. I say sort-of because I think it really isn't including the range of cells. If "PQ" is in cell A5 for example, the formula returns a 0. I was hoping it would return a 1. It will only return a 1 if "PQ" is in cell A4 and "Actual Straight" is in cell C4.


My goal:
If cell A5 = "PQ" and any cell in range C4:C8 = "Actual Straight", I want the formula to return a "1".
If the above condition is not met, I want formula to return a blank cell.

 

[Michael M]

MAybe this instead....

=SUMPRODUCT(--(C4:C8="Actual Straight")*(A5="PQ"))

 

[JimmieNeu]

That worked!!

I changed it to this to get rid of the "0" return.

=IF(SUMPRODUCT(--(C4:C8="Actual Straight")*(A5="PQ"))=0,"",(SUMPRODUCT(--(C4:C8="Actual Straight")*(A5="PQ"))))

 

[Michael M]

you could also simply change the format of the cell to a Custom format and use my original posted formula

[=0]"";General

 

[Tetra201]

Try

=IF(AND(A5="PQ",COUNTIF(C4:C8,"Actual Straight")),1,"")

 

[JimmieNeu]

Try

=IF(AND(A5="PQ",COUNTIF(C4:C8,"Actual Straight")),1,"")

That works as well!
THANK YOU!


How would I add "Fish Bench" to the formula?
"PQ" in cell A5, if either "Fish Bench" or "Actual Straight" are in cells C4:C8, the formula would return a 1 or a blank cell.

 

[Tetra201]

You are welcome.

Here is an updated formula for you to try:

=IF(AND(A5="PQ",SUM(COUNTIF(C4:C8,{"Actual Straight","Fish Bench"}))),1,"")

 

[JimmieNeu]

You are welcome.

Here is an updated formula for you to try:

=IF(AND(A5="PQ",SUM(COUNTIF(C4:C8,{"Actual Straight","Fish Bench"}))),1,"")

Awesome!!

I was trying this:

=IF(AND(A5="PQ",COUNTIF(C4:C8,{"Actual Straight";"Fish Bench:})),1,"")