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

Find more posts tagged with

Sheets

Formulas

Support

Solution Center

functionality

Accepted answers

Werner Gerstacker

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.

All comments

Werner Gerstacker

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.

Carolina Rivera

https://community.smartsheet.com/discussion/comment/252606#Comment_252606

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

Carolina Rivera

https://community.smartsheet.com/discussion/comment/252606#Comment_252606

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?

Werner Gerstacker

Hi @Carolina Rivera

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.

Carolina Rivera

https://community.smartsheet.com/discussion/comment/252613#Comment_252613

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)

Werner Gerstacker

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

Carolina Rivera

https://community.smartsheet.com/discussion/comment/252616#Comment_252616

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

Werner Gerstacker

Can you post another screenshot?

Carolina Rivera

https://community.smartsheet.com/discussion/comment/252619#Comment_252619

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

Werner Gerstacker

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?

Carolina Rivera

https://community.smartsheet.com/discussion/comment/252622#Comment_252622

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.