=COUNT(DISTINCT(COLLECT formula

shawna kalke

Hi,

I have a =COUNT(DISTINCT(COLLECT formula that is working in my sheet summary area. However when I try to recreate it in a metric sheet that pulls data from my sheet it only counts 1.

Here is the formula that is working in my sheet summary.

=COUNT(DISTINCT(COLLECT(Customer:Customer, [Who is your audience]:[Who is your audience], ="customer facing")))

Here is the formula that is NOT working in my metric sheet. It gives me a count of 1 when it should be 209.

=COUNT(DISTINCT(COLLECT({CX Global Presales Design Services Project Range 8}, {CX Global Presales Design Services Project Range 9} = "customer facing")))

Any idea what i am doing wrong on my metric sheet? Also how can i add a date range to the one being used on my metric sheet?

Thank you in advance for your time,

Genevieve P.

Hi @shawna kalke

It looks like your second formula is just missing a comma after the second range. Try this:

=COUNT(DISTINCT(COLLECT({CX Global Presales Design Services Project Range 8}, {CX Global Presales Design Services Project Range 9}, "customer facing")))

If this doesn't work it would be helpful to see some screen captures of the two ranges (Range 8 and Range 9) as well as the formula in the destination sheet...but please block out any sensitive data.

In regards to adding a date range, would you be able to provide a little more detail? If you have a date column, you could do add in the range twice with two different criteria (using the DATE function) to look between one date and another, like so:

{Date Range}, >=DATE(YYY,MM,DD), {Date Range}, <=DATE(YYY,MM,DD)

So for your formula, if your Date Range was Range 10 and you were looking between Jan 1st and Jan 15th, it could look like this:

=COUNT(DISTINCT(COLLECT({CX Global Presales Design Services Project Range 8}, {CX Global Presales Design Services Project Range 9}, "customer facing", {CX Global Presales Design Services Project Range 10}, >=DATE(2020,1,1), {CX Global Presales Design Services Project Range 10}, <=DATE(2020,01,15))))

Let me know if this makes sense or if you need any further clarification or help!

Cheers,

Genevieve

Genevieve P.

Hi @shawna kalke

It looks like you worked this out, which is great! Yes, you can add 3 different COUNTS together with a +. The only thing to keep in mind is that you could potentially get duplicates with this formula.

For example, if one row meets the criteria "<>" in Range 12, and also has "<>" in Range 10, this will be counted as 2 (once in each of your COUNTIFS formulas) versus being counted as 1 row. Is that what you wanted to do?

In regards to the T dropping off the range name, this may have to do with the sheet name, has it been adjusted recently? Cross sheet references are assigned a default name when you create them, which is the Sheet Name + Range Number.

You can change this name to be something easier to identify, if you'd prefer, such as the title of the column. Click on the reference within the formula, then select Edit Reference in the pop-up. Then you can adjust the title in the Sheet Reference Name section of that window.

Cheers!

Genevieve

Genevieve P.

Hi @shawna kalke

It looks like your second formula is just missing a comma after the second range. Try this:

=COUNT(DISTINCT(COLLECT({CX Global Presales Design Services Project Range 8}, {CX Global Presales Design Services Project Range 9}, "customer facing")))

If this doesn't work it would be helpful to see some screen captures of the two ranges (Range 8 and Range 9) as well as the formula in the destination sheet...but please block out any sensitive data.

In regards to adding a date range, would you be able to provide a little more detail? If you have a date column, you could do add in the range twice with two different criteria (using the DATE function) to look between one date and another, like so:

{Date Range}, >=DATE(YYY,MM,DD), {Date Range}, <=DATE(YYY,MM,DD)

So for your formula, if your Date Range was Range 10 and you were looking between Jan 1st and Jan 15th, it could look like this:

=COUNT(DISTINCT(COLLECT({CX Global Presales Design Services Project Range 8}, {CX Global Presales Design Services Project Range 9}, "customer facing", {CX Global Presales Design Services Project Range 10}, >=DATE(2020,1,1), {CX Global Presales Design Services Project Range 10}, <=DATE(2020,01,15))))

Let me know if this makes sense or if you need any further clarification or help!

Cheers,

Genevieve

shawna kalke

Hi Genevieve,

You are amazing. THANK YOU SO MUCH!!!!!! Both your correction and your date formula WORK!!!

I have an issue with one more countifs formula. I am not sure why I can't work it out and it should be an easy one.

I need to count three columns together , that does not include blanks , that are between dates, all from another sheet. Do you know how to achieve this?

Shawna

shawna kalke

Hi Again,

I worked out a formula to do what i want for the second one. However is there a better what to write it?

=COUNTIFS({Design Services Projec Range 12}, "<>", {Design Services Project Range 4}, >=DATE(2020, 7, 1), {Design Services Project Range 4}, <=DATE(2020, 9, 30)) + COUNTIFS({Design Services Projec Range 10}, "<>", {Design Services Project Range 4}, >=DATE(2020, 7, 1), {Design Services Project Range 4}, <=DATE(2020, 9, 30)) + COUNTIFS({Design Services Projec Range 13}, "<>", {Design Services Project Range 4}, >=DATE(2020, 7, 1), {Design Services Project Range 4}, <=DATE(2020, 9, 30))

Also not sure why when i link the sheet it is now dropping the T on project.

Genevieve P.

Hi @shawna kalke

It looks like you worked this out, which is great! Yes, you can add 3 different COUNTS together with a +. The only thing to keep in mind is that you could potentially get duplicates with this formula.

For example, if one row meets the criteria "<>" in Range 12, and also has "<>" in Range 10, this will be counted as 2 (once in each of your COUNTIFS formulas) versus being counted as 1 row. Is that what you wanted to do?

In regards to the T dropping off the range name, this may have to do with the sheet name, has it been adjusted recently? Cross sheet references are assigned a default name when you create them, which is the Sheet Name + Range Number.

You can change this name to be something easier to identify, if you'd prefer, such as the title of the column. Click on the reference within the formula, then select Edit Reference in the pop-up. Then you can adjust the title in the Sheet Reference Name section of that window.

Cheers!

Genevieve

Matt Stewart

@Genevieve P.

How can i do this without double counting?

Ive got a summary metric sheet im trying to pull in the # of projects a person has with a stage gate due in next 30 days. We have different levels of assignment, that may or may not be the same person.

For example, an Art Director may also be the Designer. So if a design is due within next 30 days, it needs to filter out the criteria as usual, but then it needs to count 1 if any of these scenarios are true:

• the Primary Column person is selected in the {Art Director} range only
• the Primary Column person is selected in the {Designer} range only
• the Primary Column person is selected in the {Art Director} range OR the {Designer} range

The first 2 scenarios are easy to do as they are separate ranges to check. But when i add these 2 scenarios to replicate scenario 3 it ends up double counting instead of counting as 1 because its the same distinct item.

my existing formulas:

Art Director:

=COUNT(DISTINCT(COLLECT({SKU Detail - Sku}, {SKU Detail - Product Designer}, $[Primary Column]@row, {SKU Detail - Delivery Month}, <>"", {SKU Detail - Parent/Children}, <>11, {SKU Detail - Uncertain}, <>1, {SKU Detail - Projected 3D Handoff}, <=(TODAY() + 30), {SKU Detail - Actual 3D Handoff}, OR(@cell = "", @cell >= TODAY()))))

Designer:

=COUNT(DISTINCT(COLLECT({SKU Detail - Sku}, {SKU Detail - Sculpt Designer}, $[Primary Column]@row, {SKU Detail - Delivery Month}, <>"", {SKU Detail - Parent/Children}, <>11, {SKU Detail - Uncertain}, <>1, {SKU Detail - Projected 3D Handoff}, <=(TODAY() + 30), {SKU Detail - Actual 3D Handoff}, OR(@cell = "", @cell >= TODAY()))))

Genevieve P.

Hi @Matt Stewart

You would want to create a third formula that COUNTS any rows that have both. Then when you have that number, you'd subtract it from the total count of the Art Director column and Designer column added together... like so:

=(COUNT(DISTINCT(COLLECT(Art Director Formula))) + COUNT(DISTINCT(COLLECT(Designer Formula)))) - COUNT(DISTINCT(COLLECT(Both Together))) 

So your third formula would be something like this:

=COUNT(DISTINCT(COLLECT({SKU Detail - Sku}, {SKU Detail - Sculpt Designer}, $[Primary Column]@row, {SKU Detail - Product Designer}, $[Primary Column]@row,{SKU Detail - Delivery Month}, <>"", {SKU Detail - Parent/Children}, <>11, {SKU Detail - Uncertain}, <>1, {SKU Detail - Projected 3D Handoff}, <=(TODAY() + 30), {SKU Detail - Actual 3D Handoff}, OR(@cell = "", @cell >= TODAY()))))

Let me know if this works for you!

Cheers,

Genevieve