Using COUNTIFS Formula
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.
Best Answer
-
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)))
Answers
-
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.
-
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.
-
Are you able to provide a screenshot for context?
-
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.
-
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)))
-
That worked perfectly. Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!