Help with Countifs and not contains formula - no longer working

Bek T

I have been calculating how many submissions in a month in one column (column A), and cross-referencing against another (column B), to ensure I'm not counting items that have a particular word in column B. I used the AI help (when it was available) and the following was working:

=COUNTIFS([Date Received]:[Date Received], CONTAINS("02", @cell), [Complainant Name]:[Complainant Name], NOT(CONTAINS("ACCRUED", @cell)))

Now, it's simply not counting anything that's come in in the month (in this case February), and I'm not sure why? No error, just not counting. I've tried a few alternatives but keep getting UNPARSEABLE message. Any help gratefully appreciated, cheers.

Bek T

Thanks @jmyzk_cloudsmart_jp. I didn't want to make another selection in my sheet summary (it's pretty long as is) so came up with a bit of a hybrid formula, and it's working (below test is for March):

=COUNTIFS([Date Received]:[Date Received], ISDATE(@cell ), [Date Received]:[Date Received], MONTH(@cell ) = 3, [Complainant Name]:[Complainant Name], NOT(CONTAINS("ACCRUED", @cell)))@cell

As I don't want to be counting names that have accrued in the name section, so it's doing that, and still returning a count for submissions received in months where there are no accruals.

FYI I'm in NZ so our date format is DD-MM-YYYY so it took me a sec to wonder why you were saying having "12/02/24" would be wrong to count it as Feb!😅

jmyzk_cloudsmart_jp

Hi @Bek T

If you want to count based on a month choice like "02," a text value, and a date value like '02/14/25' or '02/14/25 11:00 AM', you can convert "02" to a number with the VALUE() function and compare it with MONTH(Date).

Using the CONTAINS function, which checks if a text is contained in a text, can lead to an error if the date is also "02", for example. ("12/02/24" is counted as a February complaint.)

In the demo sheet below, I used the following Sheet Summary fileds formulas;

[Date Count]

=COUNTIFS(Date:Date, ISDATE(@cell), Date:Date, MONTH(@cell) = VALUE([Month Number]#), [Complainant Name]:[Complainant Name], "ACCRUED")

As I tested, I had to put 'ISDATE(@cell)' conditions because later, when trying to apply MONTH(@cell), errors will happen in rows with no date data.

https://app.smartsheet.com/b/publish?EQBCT=99117991c36b4d9f93de2fa3ad23c985

Bek T

Thanks @jmyzk_cloudsmart_jp. I didn't want to make another selection in my sheet summary (it's pretty long as is) so came up with a bit of a hybrid formula, and it's working (below test is for March):

=COUNTIFS([Date Received]:[Date Received], ISDATE(@cell ), [Date Received]:[Date Received], MONTH(@cell ) = 3, [Complainant Name]:[Complainant Name], NOT(CONTAINS("ACCRUED", @cell)))@cell

As I don't want to be counting names that have accrued in the name section, so it's doing that, and still returning a count for submissions received in months where there are no accruals.

FYI I'm in NZ so our date format is DD-MM-YYYY so it took me a sec to wonder why you were saying having "12/02/24" would be wrong to count it as Feb!😅

jmyzk_cloudsmart_jp

Glad you find a solution!😁

Bek T

Appreciate your help with this @jmyzk_cloudsmart_jp, wouldn't have found the solution without your help!