Count If Formula for 2 Separate Criteria Not Correct

Hello All,

I am trying to write a CountIf Excel formula to calculate 2 separate criteria for a range. The range consists of an If formula that generates either a 1 or a 2, so the Countif will tell the user what category that range is ( 1 or 2). The formula works when it is only for one criteria but I can't get it to work when I add a second CountIf. I appreciate you taking time to read my post. Please let me know if you have any suggestions, thank you!

1 criteria which works =IF(COUNTIF(M14:AR14,"*"&"1"&"*"),"Check","x")

2 criteria =IF(COUNTIF(M14:AR14,"*"&"1"&"*"),"Check",IF(COUNTIF(M14:AR14,"*"&"4"&"*"),"Clear","x"))

Translating formula 1 into words: If there are any cells in row 14 that contain a 1, then return "check". If not, return "x".
Translating formula 2 into words: If there are any cells in row 14 that contain a 1, then return "check". If not, then test if there are any cells in row 14 that contain a 4. If any cells contain a 4, then return "Clear". If not return "x".

Your description of what is in row 14 suggests that the cells in row 14 will either be a 1 or a 2, but never a 4. If that is correct, then formula 2 will return "Check" when it is a 1, an "x" when it is a 2, and will never return "Clear".

From there, I am not sure exactly what logic you are trying to implement here, so I cannot suggest any corrections or changes.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

You should just be able to use:

Please Login or Register  to view this content.

Does that give you the results you're looking for?

If I understand what you're trying to do, this should work:

Please Login or Register  to view this content.

If the date a 4 is found on is equal to today, Check will show.

The ranges you mentions are different, but this should work in your workbook from post #1, in cell A4 on sheet End:

Please Login or Register  to view this content.

If I've got the ranges right, this should work for your updated ranges:

Please Login or Register  to view this content.

Does that give you the results you're looking for?