Using COUNTIFS Formula

jtomeaCH

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.

Paul Newcome

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

Paul Newcome

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.

jtomeaCH

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.

Paul Newcome

Are you able to provide a screenshot for context?

jtomeaCH

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.

Paul Newcome

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

jtomeaCH

That worked perfectly. Thank you!

Paul Newcome

Happy to help. 👍️