Do I need a COUNTIF for this scenario?

I'm using the following formula (which works perfectly thanks to this amazing community!) to count the number of distinct installers for a given week. Range 1 is installers Name and Range 2 is a week ending date.

=COUNT(DISTINCT(COLLECT({Field Production Report 2024 Range 1}, {Field Production Report 2024 Range 2}, <=[Week Ending]@row, {Field Production Report 2024 Range 2}, >([Week Ending]@row - 7))))

I have an additional column to designate what kind of install it is, either Res or Com (found in Range 3) and would like to get distinct counts based on this additional criteria. So I thought what if I make this a COUNTIF. I've tried it at the front and tail end but I can't get it to work.

Any suggestions would be greatly appreciated. Thank you...

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    Is this the type of result you are looking for? If so, you can simply add the type of install as an additional condition within the COLLECT statement. These would be your formulas for Res and Com columns:

    =COUNT(DISTINCT(COLLECT({Field Production Report 2024 Range 1}, {Field Production Report 2024 Range 2}, <=[Week Ending]@row, {Field Production Report 2024 Range 2}, >([Week Ending]@row - 7), {Range 3}, "Res")))

    =COUNT(DISTINCT(COLLECT({Field Production Report 2024 Range 1}, {Field Production Report 2024 Range 2}, <=[Week Ending]@row, {Field Production Report 2024 Range 2}, >([Week Ending]@row - 7), {Range 3}, "Com")))

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    Is this the type of result you are looking for? If so, you can simply add the type of install as an additional condition within the COLLECT statement. These would be your formulas for Res and Com columns:

    =COUNT(DISTINCT(COLLECT({Field Production Report 2024 Range 1}, {Field Production Report 2024 Range 2}, <=[Week Ending]@row, {Field Production Report 2024 Range 2}, >([Week Ending]@row - 7), {Range 3}, "Res")))

    =COUNT(DISTINCT(COLLECT({Field Production Report 2024 Range 1}, {Field Production Report 2024 Range 2}, <=[Week Ending]@row, {Field Production Report 2024 Range 2}, >([Week Ending]@row - 7), {Range 3}, "Com")))

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭

    You would only need to add it to your collect formula. The collect statement only collects the data that matches the criteria included in the statement.

  • tgattsh
    tgattsh ✭✭✭✭✭

    @Carson Penticuff, @Hollie Green Thank you for your response.

    @Carson Penticuff Thanks for the added clarification, very insightful, not to mention helpful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!