Count based on two criteria

JJMunoz · 04/29/22

Hi,

This is just an example below. I am trying to get a formula for the 'count' column. I want to return a number from 'dates' and 'group'. If there are two of the same dates with the same group, I want a '2' in that cell. If there is only one date for that group, I want a '1' in that cell. Does that make sense?

TIA

Genevieve P. · 05/03/22

Hi@JJMunoz

the @row is a function that needs to be typed out in lower case. Try:

=COUNTIFS([PARTURITION DATE]:[PARTURITION DATE], [PARTURITION DATE]@row, COMPOUND:COMPOUND, COMPOUND@row)

See:Create Efficient Formulas with @cell and @row

Cheers!

Genevieve

Jeff Reisman · 04/29/22

@JJMunozTry this:

=COUNTIFS(date:date,[email protected], group:group,[email protected])

This says count the rows where the date is the same as this row and the group is the same as this row.

JJMunoz · 05/02/22

Thanks- I tried that and #unparseable is coming up.

Genevieve P. · 05/03/22

Hi@JJMunoz

In Jeff's example, the column names would need to be the same as your column names. Your screen capture has the text "Date" and "Group" in a cell, so the formula is:

=COUNTIFS(date:date,[email protected], group:group,[email protected])

然而如果列,你的约会对象是行为ually called "Start Date", then you'd need to update it to:

=COUNTIFS([Start Date]:[Start Date], [Start Date]@row, group:group,[email protected])

Does that make sense? Here's an article that goes through referencing columns in formulas:Create a Cell or Column Reference in a Formula

If this hasn't helped, it would be useful to see a screen capture of the actual sheet including column names and identifying column types, but please block out sensitive data.

Cheers,

Genevieve

Jeff Reisman · 05/03/22

Hi @JJMunoz<\/a> <\/p>

In Jeff's example, the column names would need to be the same as your column names. Your screen capture has the text "Date" and "Group" in a cell, so the formula is:<\/p>

=COUNTIFS(date:date, date@row, group:group, group@row)<\/p>

However if the column that houses your date is actually called "Start Date", then you'd need to update it to:<\/p>

=COUNTIFS([Start Date]:[Start Date], [Start Date]<\/strong>@row, group:group, group@row)<\/p>

Does that make sense? Here's an article that goes through referencing columns in formulas: Create a Cell or Column Reference in a Formula<\/a><\/p>

If this hasn't helped, it would be useful to see a screen capture of the actual sheet including column names and identifying column types, but please block out sensitive data.<\/p>

Cheers,<\/p>

Genevieve<\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"JJMunoz\",\"userID\":147510}}},{\"insert\":\" \\nIn Jeff's example, the column names would need to be the same as your column names. Your screen capture has the text \\\"Date\\\" and \\\"Group\\\" in a cell, so the formula is:\\n=COUNTIFS(date:date, date@row, group:group, group@row)\\nHowever if the column that houses your date is actually called \\\"Start Date\\\", then you'd need to update it to:\\n=COUNTIFS(\"},{\"attributes\":{\"bold\":true},\"insert\":\"[Start Date]:[Start Date], [Start Date]\"},{\"insert\":\"@row, group:group, group@row)\\nDoes that make sense? Here's an article that goes through referencing columns in formulas: \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2476816-create-cell-column-reference-formula\"},\"insert\":\"Create a Cell or Column Reference in a Formula\"},{\"insert\":\"\\nIf this hasn't helped, it would be useful to see a screen capture of the actual sheet including column names and identifying column types, but please block out sensitive data.\\nCheers,\\nGenevieve\\n\"}]","format":"rich","dateInserted":"2022-05-03T10:17:02+00:00","insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-19T18:13:12+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/328101#Comment_328101","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/328101#Comment_328101

Thanks for jumping in, Genevieve.

@JJMunozit always helps to provide screenshots of column names, data layout, etc and include any formulas you have tried and the resulting error messages. With this info, most of the time other users or the real experts in this community can get you pointed in the right direction pretty quickly.

JJMunoz · 05/03/22

@Jeff Reisman @Genevieve P.Thank you both. What is still wrong?

Here is a screenshot. This is the current formula in the cell.

=COUNTIFS([PARTURITION DATE]:[PARTURITION DATE], [PARTURITION DATE]@ROW, COMPOUND:COMPOUND,[email protected])

Genevieve P. · 05/03/22

Hi@JJMunoz

the @row is a function that needs to be typed out in lower case. Try:

=COUNTIFS([PARTURITION DATE]:[PARTURITION DATE], [PARTURITION DATE]@row, COMPOUND:COMPOUND, COMPOUND@row)

See:Create Efficient Formulas with @cell and @row

Cheers!

Genevieve

JJMunoz · 05/03/22

@Genevieve P.That was it! Thank you.. I'm new to this, so I appreciate the help!

Genevieve P. · 05/03/22

No problem! We've all been new to this at some point

That's where the Community is great - there are so many wonderful Smartsheet users like@Jeff Reismanwho can help answer questions!

I would also recommend theSmartsheet Universityif your plan has access to it, and the freeOnboarding Webinars, here.

Cheers,

Genevieve

Jeff Reisman · 05/03/22

@JJMunoz

The two links in my signature below go to the Functions List (which links to all their help pages) and to the Formula Error Messages list. Both are invaluable resources for working with Smartsheet formulas, I use them just about every day.

Another very helpful resource can be found in the Solution Center. Search for "formula examples" and you'll find a template you can add to your Smartsheet folders. This template contains real examples for all the functions, beyond what you get on the Function help pages.

Count based on two criteria

Best Answer

Answers

Help Article Resources

Categories