Count based on two criteria

JJMunoz

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.

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

@JJMunoz Try this:

=COUNTIFS(date:date, date@row, group:group, group@row)

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

JJMunoz

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

Genevieve P.

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, date@row, group:group, group@row)

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, group@row)

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

https://community.smartsheet.com/discussion/comment/328101#Comment_328101

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.

JJMunoz

@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, COMPOUND@ROW)

Genevieve P.

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

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

Genevieve P.

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

Jeff Reisman

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