Hannah has attended:
Excel Dashboards for Business Intelligence course
COUNTIFS
Hi,
I'm trying to construct a COUNTIFS formula but am struggling with the last element. I would like it to count records that have:
- 'Achieved' in column B
- '2014/15' in column W
- a date in column W that is before or equal to the date in column U + 90 days.
Many thanks,
Hannah
RE: COUNTIFS
Hi Hannah
Thanks for your question.
The answer will depend on the way you've set your spreadsheet up.
Can I ask what format the data in column W is in? Is it text eg '2014/15' or date eg '01/01/15'?
Regards
Sarah
Excel Trainer
RE: COUNTIFS
Hi,
I made an error with the question I asked, it should have read:
I'm trying to construct a COUNTIFS formula but am struggling with the last element. I would like it to count records that have:
- 'Achieved' in column B (plain text)
- '2014/15' in column W (plain text)
- a date in column X that is before or equal to the date in column V + 90 days. (date format)
Many thanks,
Hannah
RE: COUNTIFS
Hi Hannah
I understand why you're having trouble with this, it's a bit fiddly!
In your last criteria you want to test each row individually to see if the date in each cell in column X<= the date in each corresponding cell in column V + 90, so you need to take one more step before using your COUNTIFS.
Try inserting a new column (let's say Y) and use an IF formula to test:
=IF(X1<=V1+90,TRUE,FALSE)
You can then use this column Y in your COUNTIFS:
=COUNTIFS(B:B,"Achieved",W:W,"2014/15",Y:Y,"TRUE")
Let me know if this works and if you have any further questions.
Regards,
Sarah
