Count HAS Function Between Dates

APK Studios

I have worked to find a solution out there but not getting one that works. I am collecting data from a sheet to be used in charts on a dashboard (so referencing another sheet).

The result will need to be generated from two columns

• Date - date the project closed
• Fund - The fund/funds used to pay for the project (multiselect dropdown)

I need to know how many times a Fund is used between two dates. Because this is a multiselect I need to use the HAS function but I cannot get it to work in the formula.

The formula below will only work if the only fund in the cell is "XXX." However if multiple funds are used the formula will not count it. No matter how I try to add the HAS formula, I get an error (I'm not going to confuse people with all my wrong attempts).

=COUNTIFS({Date}, >=DATE(2024, 7, 1), {Date}, <=DATE(2025, 6, 30), {Fund}, "XXX")

Any assistance would be appreciated.

DKazatsky2

Hi @APK Studios,

Give this a try.

=COUNTIFS({Date}, >=DATE(2024, 7, 1), {Date}, <=DATE(2025, 6, 30), {Fund}, HAS(@cell, "XXX"))

Hope this helps,

Dave

DKazatsky2

Hi @APK Studios,

Give this a try.

=COUNTIFS({Date}, >=DATE(2024, 7, 1), {Date}, <=DATE(2025, 6, 30), {Fund}, HAS(@cell, "XXX"))

Hope this helps,

Dave

APK Studios

YAY!! This worked!!!

I was missing the whole @Cell concept. Now that I have been pointed that direction I understand it's need. The help page literally would have answered the question if I knew to look.

"When you want to perform calculations in formulas that look at ranges of cells, for example: SUMIF, SUMIFS, COUNTIF, and COUNTIFS, you can use the @cell argument in the criteria of the function. The @cell argument performs a calculation on each row at the same time that the primary function (SUMIF for example) is evaluating the criteria in the range, making your formula more efficient."

Thank you!