Do I need a COUNTIF for this scenario?

tgattsh

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

Carson Penticuff

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

Carson Penticuff

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

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

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

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