Help! Cannot get COUNTIFS formula to work

Paul Newcome

What does this one do?

=COUNTIFS({Multi-County Range 1}, CONTAINS("T2", @cell))

KUMDC

@Paul Newcome When I put that in it gives me all T2's for the previous year as well as the current time frame and if I add in the range for dates it gives me an #unparsable error.

Paul Newcome

Can you copy/paste from the sheet the one throwing that error when you add in the range for the dates?

KUMDC

@Paul Newcome

=COUNTIFS({Multi-County Range 1}, CONTAINS("T2", @cell)), {Multi-County Range 2}, AND(IFERROR(MONTH(@cell), 0) >= 7, IFERROR(MONTH(@cell), 0) <= 9, IFERROR(YEAR(@cell), 0) = 2023))

Paul Newcome

What happens when you remove one of the closing parenthesis from after the CONTAINS function?

KUMDC

@Paul Newcome It works...lol... now how do I get it to count all that plus give the count by person?

Paul Newcome

Now we should just have to add that in as another range/criteria set.

=COUNTIFS({Multi-County Range 1}, CONTAINS("T2", @cell)), {Multi-County Range 2}, AND(IFERROR(MONTH(@cell), 0) >= 7, IFERROR(MONTH(@cell), 0) <= 9, IFERROR(YEAR(@cell), 0) = 2023), {Multi-County Range 3}, @cell = "Cheyney Cushing")

KUMDC

@Paul Newcome

That worked! Would it be possible to use the CONTAINS("Cheyney Cushing", @ cell) - in the off chance more than one team member works on a "T2"? And if so, what would that look like?

And to continue for the other sheets I need it to look at for data, would I just use this formula and add +COUNTIFS for the remaining sheets?

KUMDC

@Paul Newcome

I tried this for my first question above and it worked.

=COUNTIFS({Multi-County Range 1}, CONTAINS("T2", @cell), {Multi-County Range 2}, AND(IFERROR(MONTH(@cell), 0) >= 7, IFERROR(MONTH(@cell), 0) <= 9, IFERROR(YEAR(@cell), 0) = 2023), {Multi-County Range 3}, CONTAINS("Cheyney Cushing", @cell))

And using that theory, I added a +COUNTIFS to have it look at an additional sheet and what do you know, it actually worked! So now I will just keep going! And thanks for always being such a HUGE help to novices like me who are trying to do things way beyond their brain power capacity!

Paul Newcome

Happy to help. 👍️

Keep in mind... If you ever decide to convert the name column to a contact type or use the same logic on a contact type column, you will need to switch over to the HAS function because the CONTAINS function does not work reliably on contact type columns.

KUMDC

@Paul Newcome

You can bet I will be coming right back here to you if that happens! In the meantime instead of a rain dance I will do a "no contact type columns" dance 🤣