# Count based on two criteria

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

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)

Cheers!

Genevieve

• ✭✭✭✭✭

@JJMunoz Try 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.

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

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

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])

However if the column that houses your date is actually 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

• ✭✭✭✭✭

Thanks for jumping in, Genevieve.

@JJMunoz it 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.

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

• @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])

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)

Cheers!

Genevieve

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

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 Reisman who can help answer questions!

I would also recommend the Smartsheet University if your plan has access to it, and the free Onboarding Webinars, here.

Cheers,

Genevieve

• ✭✭✭✭✭

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.

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US