COUNTIFS function with two criteria
Hi all!
I'm currently trying to count how many red entries (in the "Puri Safety" column) are in the month of June (using the "Created" column). I'm trying to use the following formula:
=COUNTIFS(Created:Created, IFERROR(MONTH(@cell), 0) = 6, [Puri Safety]:[Puri Safety], "Red")
However, it returns a value of zero... what must be the problem? Please find attached a screencap of the grid to better visualize my issue. (I would also like to count the red entries per day, would the formula be similar?)
Best,
Carolina Rivera
Best Answer
-
Hi @Carolina Rivera ,
If I set this up one-for-one it works fine - BUT: the 'Created' column needs to be a date type column. As soon as I change it to e.g. a text/number column your formula results in a 0.
If you want to do these counts per day, a simple countifs replacing the part 'IFERROR(MONTH(@cell), 0)' with a reference to the cell that holds the date you want to count for should be fine.
Answers
-
Hi @Carolina Rivera ,
If I set this up one-for-one it works fine - BUT: the 'Created' column needs to be a date type column. As soon as I change it to e.g. a text/number column your formula results in a 0.
If you want to do these counts per day, a simple countifs replacing the part 'IFERROR(MONTH(@cell), 0)' with a reference to the cell that holds the date you want to count for should be fine.
-
Thanks so much!!!! I've been looking at this for hours and it slipped my mind that the column had to be in the 'date' type...
-
Hi again! I tried to put together the formula for the daily count:
=COUNTIFS([Puri Safety]:[Puri Safety], "Red", [Action Creation Date]:[Action Creation Date])
However, this is what the counter says: #INCORRECT ARGUMENT SET ... Should I specify the day that I want to count?
-
As I wrote earlier, you need to 'replace the part 'IFERROR(MONTH(@cell), 0)' with a reference to the cell that holds the date you want to count for'.
Arguments for COUNTIFs come in pairs:
1) The range you want to evaluate, e.g. [Puri Safety]:[Puri Safety] or [Action Creation Date]:[Action Creation Date]
2) And the criteria you want to use for the evaluation.
For 2) you used "Red" for the range [Puri Safety]:[Puri Safety] but the criterion for the range [Action Creation Date]:[Action Creation Date] is missing, you still need to provide it as the last element in your formula. You can start with [Action Creation Date]@row but this will enter the same count into every row with the same date.
-
Thanks so much for responding, I tried two formulas, the first one returns #INVALID DATA TYPE, while the second one returns #UNPARSEABLE:
(1) =COUNTIFS([Puri Safety]:[Puri Safety], "Red", [Action Creation Date]:[Action Creation Date], @cell)
(2) =COUNTIFS([Puri Safety]:[Puri Safety], "Red", [Action Creation Date]:[Action Creation Date]@cell)
-
Try =COUNTIFS([Puri Safety]:[Puri Safety], "Red", [Action Creation Date]:[Action Creation Date], [Action Creation Date]@row)
[Action Creation Date]:[Action Creation Date] provides the range to search in
[Action Creation Date]@row is the value that needs to be matched in the range in order for it to be counted
-
It worked! but now I'm getting the same value for every day.
-
Can you post another screenshot?
-
Sure thing! The highlighted squares are the columns I'm trying to reference
-
Hmmm - the entries in the 'Puri Daily' columns aren't really dates, they are weekdays - so I'm surprised that you get any result at all.
It's also strange that the result for Jun in your column Count.. is 3, it should be 9, correct?
-
Yes... as for the result in Jun, it should be 3, because there are only 3 red entries in the month of June. I would think that the solution for the formula shouldn't be that difficult, since I just want to count daily actions... I guess that it'll be fixed in a sudden "eureka" moment...
Help Article Resources
Categories
Check out the Formula Handbook template!
