Countif Exception Advice Needed!

[Makensmoke]

Hi, thank you for reading this.

I'm trying to count individual cells in a range that count "this", but not the cells (ignore the cells) in that range that contain "that".

My attempts are failed as when excel sees a row with my <> (exception) in it, it seems to not be counting the row.

In more depth, count column A1:L60 (these ranges are actually a month of a calendar) that includes B1(is actually a persons name) but don't include cells that say "holiday". The "holiday" cells are also green, which could be used as a deterrent for the count as well but that looks a little more advanced than my boundaries as well.

Using Excel 2016.

Thank you SO much.

Al

[JohnTopley]

Attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[bebo021999]

Actually, to count B1 in range:

=COUNTIF(A1:L60,"*"&B1&"*")

and count "holiday":

=COUNTIF(A1:L60,"*holiday*")

so the expected formula should be:

=COUNTIF(A1:L60,"*"&B1&"*")-COUNTIF(A1:L60,"*holiday*")

[Makensmoke]

Thank you.

We're not quite there...sorry.

I need to count only the cells that do not include the text "holiday". So in other words, how many times in a range is Joe populated (listed) OTHER THAN those days (cells) that include the text "holiday".

The above example will count Joes, and holidays and sum them...I only need to count Joes days that are not a...Joe holiday.



I'll upload a desensitized example here, thanks guys for taking the time to help.

[sandy666]

something like that?

Please Login or Register  to view this content.

[Makensmoke]

Sandy,

Thank you but again, we your example is subtracting all ("holidays"), from (all cells). I'm trying to count how many cells Joe is in except for cells that also include the word holiday.

Al

[davsth]

try =COUNTIF(A1:N30,"*joe*")-COUNTIFS(A1:N30,"*joe*",A1:N30,"*holiday*")

[sandy666]

so you want count how many "Joe" minus "Joe" and "Holiday"?

Please Login or Register  to view this content.

or if you need another way - change criteria.
Or I am too stupid to understand