Count how many within a year

littlekay32

Hi All, I have column title named 'Type of Request' and you can click multiple answers, so a cell can contain 1 or maybe 4 answers for example... . I have another column called 'Date Created' which is the date of the request. What i am trying to do is a formula that will tell me how many requests we have had within the finical year. 2022, 04, 01 >2023, 03, 31.... i am struggling to find something that works... baring in mind Type of Request can have multiple... i can do a 'Total' altogether but struggling to do it based on date. I have searched similar things but having no joy.

i currently have.... =COUNTIFS([Date contacted]:[Date contacted], >=DATE(2022, 4, 1), [Date contacted]:[Date contacted], <=DATE(2023, 3, 31))

Which is working well to count how many rows between those dates, but struggling when i change it to =COUNTIFS([Type of Request]:[Type of Request], >=DATE(2022, 4, 1), [Date contacted]:[Date contacted], <=DATE(2023, 3, 31))

i am currently trying.... and it appears to be working but not 100%

=COUNTM([Type of Request]:[Type of Request], HAS(>=DATE(2022, 4, 1), [Date contacted]:[Date contacted], <=DATE(2023, 3, 31)))

Still keep playing with it and it isnt doing it :(

Hollie Green

I would use a =Join/Collect Formula to get all of the items into one cell then count the number in that cell.

I used a similar formula that gathered the information from a different sheet you may have to add an @cell for it to work on the same sheet not positive without testing. Below is what I would try.

=Join(Collect([Type of Request]:[Type of Request],[Date Contacted]:[Date Contacted], >=DATE(2022, 4, 1), [Date contacted]:[Date contacted], <=DATE(2023, 3, 31)),Char(10))

Then reference the helper cell you created with a CountM formula.