# COUNTIFS function with two criteria

Options
edited 06/04/20

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

• Options

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...

• Options

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?

• ✭✭✭✭✭
Options

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.

• Options

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)

• ✭✭✭✭✭
Options

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

• edited 06/05/20
Options

It worked! but now I'm getting the same value for every day.

• ✭✭✭✭✭
Options

Can you post another screenshot?

• Options

Sure thing! The highlighted squares are the columns I'm trying to reference

• ✭✭✭✭✭
Options

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?

• Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!