Help with COUNTIFS

isd3544

Hi Community!

I am hoping someone can help me figure out how to correct this formula or create a new one. I want to count how often the regional VP’s name appears in a row with a date within a specific date range (quarter). Current formula I am using in a calculation sheet and referencing another:

=COUNTIFS({TLH User Engagement Range 3}, "Sanchez", {TLH User Engagement Range 6}, AND(@cell >= DATE(2024, 1, 1), @cell <= DATE(2024, 3, 31)))

It is returning a count of 0 but there a 4 rows that should meet the criteria.

Thanks!

isd3544

Fixed. The errors in the last cells of the Date Helper column have cleared. The Calculations sheet still reads 0 for Qtr.

Paul Newcome

Ok. So your formula is looking for an exact match on "Sanchez", but it looks more like you want to count rows where the cell contains "Sanchez" as part of a longer text string. In that case, try adjusting that criteria from

"Sanchez"

to

CONTAINS("Sanchez", @cell)

Paul Newcome

Are you able to provide a screenshot of the source data that should be getting counted?

isd3544

Hopefully this helps!

Paul Newcome

That looks like text data to me. Try inserting a date type helper column and use this as a column formula (then reference the helper column as your date range in the COUNTIFS):

=DATE(VALUE(LEFT([User Last Access Date]@row, 4)), VALUE(MID([User Last Access Date]@row, 6, 2)), VALUE(MID([User Last Access Date]@row, 9, 2)))

isd3544

Still returning 0.

Paul Newcome

Ok. Lets next try wrapping that whole DATE piece in an IFERROR.

=IFERROR(DATE(…………………), "")

isd3544

Still '0". Hopefully, I entered the IFERROR correctly?

Paul Newcome

Sorry. I meant wrapping the DATE formula in the [DAte Helper] column in the IFERROR.

isd3544

Ok. I am receiving #INCORRECT ARGUMENT SET

Paul Newcome

Looks like you just forgot that last part of the IFERROR that outputs a blank.

=IFERROR(DATE(…………………), "")

isd3544

Fixed. The errors in the last cells of the Date Helper column have cleared. The Calculations sheet still reads 0 for Qtr.

Paul Newcome

Ok. So we should have the date part sorted. Next is the "Sanchez" part. Is {Range 3} visible in any of your screenshots already provided?

isd3544

Yes. It is the column "Branch Path".

Paul Newcome

Ok. So your formula is looking for an exact match on "Sanchez", but it looks more like you want to count rows where the cell contains "Sanchez" as part of a longer text string. In that case, try adjusting that criteria from

"Sanchez"

to

CONTAINS("Sanchez", @cell)

isd3544

That's it! It is working now. I thank you for your patience and assistance!

Paul Newcome

Happy to help. 👍️