Format for Countifs specifc data AND date field

Hi All,

I have to survey the audiences to our events, and i eventually get an excel file with all the results in of the surveys. for instance 1 column would hold the age bracket of the respondents. 0 to 15, 16-19 etc.

I have to report to my funders in a specific format - by quarter April to June, July to September etc - the count of 16-19 year olds in each quarter.

I can easily do =COUNTIF(T1:T1000,"16-19") which gives me the total number of 16-19 year olds overall.

BUT I would really like the formula to only give me the number of 16-19 year olds in each quarter.

I realise that this involves COUNTIFS, but i cant seem to get the syntax right.

any help gratefully received!

thanks

ian

Apologies Ali, and everyone.

I have put some dummy data in this spreadsheet and on another Tab the funders form I have to fill in.

Thanks

Ian

Thanks Marvin. I haven't used Pivot Tables before, so I will def look into it. I assume they work with text fields too? as in the age range will be a text field "16-19".

Always excited to learn something new in excel.

Thanks again,

Ian

Thanks Marvin, I am sure I can get my head around Pivot charts at some point. I appreciate you doing a sample for me.

I wish there was an easier method at this point using COUNTIFS though.

Ian

Hi John,

That's it! Thank you. Yes, when i put the dates in properly, the correct numbers came through.

Many thanks!

Ian

You could make life easier by changing the "AGE" in column C to "0-15", "16-19" etc then insert (for example) C20 to replace "16-19" in the formula I provided thus you can simply copy the formula down column E for the AGE analysis.

In similar way, you could add cells with Qtr start/end dates so you do not have to repeatedly hard code them in your formulae.

Yes!! I like that plan a lot. I can add the actual text of the Age range in the notes section (without messing with the funders form, and reference those cells in the formulae.

If I add the start and end dates to the notes field for easy reference, i assume i replace the "<=" &DATE(2021, 4,1) with "<=" & N9 (N9 being where i placed the Quarter start date.)?

i'll give it a go now.

Ian

"<=" & N9

Yes... that is correct.

works a treat, many thanks John.

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Thanks John, I'll mark as solved. I believe I have already - if thanking is the same as adding to reputation. :-)