May 06 2023 05:23 PM
=COUNTIFS('Coaching log'!A:A,MONTH(D2),'Coaching log'!D:D,"MTAT")
Situation:
Match month value from Snapshot D2 to Coaching log column A. Then Count text MTAT occurrence from Coaching log column D.
I have no error on formula but it is pulling value 0. I have 3 MTAT for Jan on spreadsheet which is not on picture below. D2 is set to Jan
May 06 2023 09:09 PM
@Ray_Ray2024 You have a few problems here.
1) D2 seems to contain the word "Jan". MONTH(D2) will raise a value error and always lead to returning zero for the entire formula.
2) Even when you enter a real date for January 1, 2023 (and format it to show only Jan) in D2, MONTH(D2) will return the number 1 and you will be checking for the dates in column A to be equal to 1. None qualify, thus the result will always be zero.
What you need to do is check if the dates in A are >= to January 1,2023 AND <= January 31, 2023 AND column D contains "MTAT".
The attached workbook contains an example of how the formula should look like. I trust you can re-work it to suit you own sheet. And one piece of advice would be not to use references to entire columns like A:A and D:D. Better to use a finite ranges.