# Using COUNTIFS Formula

Options
✭✭
edited 06/22/23

Is there a way to use the COUNTIFS function (or another function) like the SUMIFS? I have this one working perfectly:

=SUMIFS({OM Book Date}, {OM Total Sell}, AND(@cell >= DATE(2023, 6, 1), @cell <= DATE(2023, 6, 30)))

Now I need it to count OM Total Sell (some are blank, I need it to count only the ones that have numbers in them) for orders that were booked within the same time frame.

• ✭✭✭✭✭✭
Options

It looks like you may need a COUNT/DISTINCT/COLLECT combo then.

=COUNT(DISTINCT(COLLECT({Work Order}, {Book Date}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2023)))

• ✭✭✭✭✭✭
Options

It would be the same except you would remove the "range to sum" portion (first range) and of course it would be a COUNTIFS instead of a SUMIFS.

• ✭✭
Options

Sorry I had the wrong formula, that one was backwards. The correct one is

=SUMIFS({OM Total Sell}, {OM Book Date}, AND(@cell >= DATE(2023, 6, 1), @cell <= DATE(2023, 6, 30)))

The difficult issue with my data is that you can have multiple dates for each line of the project (each PO line has the book date on it), but only one total sell line. I am hoping to count the total sell line so that I count each individual project as one instead of each line, which would show more projects that we have due to the multiple lines per project.

• ✭✭✭✭✭✭
Options

Are you able to provide a screenshot for context?

• ✭✭
Options

I made up a small snip since my file has so many columns. I am trying to make the count come back with the result of 2 in this instance. If I count by book date, I would get 4, but there are only 2 jobs.

• ✭✭✭✭✭✭
Options

It looks like you may need a COUNT/DISTINCT/COLLECT combo then.

=COUNT(DISTINCT(COLLECT({Work Order}, {Book Date}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2023)))

• ✭✭
Options

That worked perfectly. Thank you!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!