Excel formula to count unique cells in column Q with multiple criteria

[Knockoutpie]

I put together this sumproduct formula which works to calculate total sales using a few critiera

• Column G Sheet 2 : brand Name
• Cell A2 Sheet 1 = Brand name
• Column AE Sheet 2 = Transaction date column - which is converted to Year (B13) and Month (C1)
• Column AY Sheet 2 = Transaction value total

=SUMPRODUCT(--('Sheet2'!$G2:$G10000='Sheet1'!$A$2),--(YEAR('Sheet2'!$AE2:$AE10000)='Sheet1'!$B13),--(MONTH('Sheet2'!$AE2:$AE10000)='Sheet1'!C$1),'Sheet2'!$AY2:$AY10000)

The end result which works: Calculates total sales based on 3 criteria, month, year and brand.
How can I modify this formula to instead calculate the total unique cells (customers) in column Q in Sheet 2?
I assumed it would be as easy as modifying my sum array which is bold in the formula above but I have not figured it out.

If it helps, i do have two formulas which gives me the total unique cell count in the column, I just can't figure out how to add either into my formula..

=COUNTA(UNIQUE('Sheet2'!Q2:Q10000))

=SUMPRODUCT(1/COUNTIF('Sheet2'!Q2:Q27932,'Sheet2'!Q2:Q10000))

 

[Fluff]

How about

=ROWS(UNIQUE(FILTER(Sheet2!$Q2:$Q10000,(Sheet2!$G2:$G10000=$A$2)*(YEAR(Sheet2!$AE2:$AE10000)=$B13)*(MONTH(Sheet2!$AE2:$AE10000)=C$1))))

 

[Knockoutpie]

How about

=ROWS(UNIQUE(FILTER(Sheet2!$Q2:$Q10000,(Sheet2!$G2:$G10000=$A$2)*(YEAR(Sheet2!$AE2:$AE10000)=$B13)*(MONTH(Sheet2!$AE2:$AE10000)=C$1))))

Nailed it! Thanks Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Knockoutpie]

You're welcome & thanks for the feedback.

Hey Fluff, side note.. could you please find a way to modify this instead of referring to C1 which is Month 1, Refer to C1+D2+E3, this will give me total unique customer count for 3 months.

 

[Fluff]

Try

=ROWS(UNIQUE(FILTER(Sheet2!$Q2:$Q10000,(Sheet2!$G2:$G10000=$A$2)*(YEAR(Sheet2!$AE2:$AE10000)=$B13)*((MONTH(Sheet2!$AE2:$AE10000)=C$1)+(MONTH(Sheet2!$AE2:$AE10000)=D2)+(MONTH(Sheet2!$AE2:$AE10000)=E3)))))

 

[Knockoutpie]

Try

=ROWS(UNIQUE(FILTER(Sheet2!$Q2:$Q10000,(Sheet2!$G2:$G10000=$A$2)*(YEAR(Sheet2!$AE2:$AE10000)=$B13)*((MONTH(Sheet2!$AE2:$AE10000)=C$1)+(MONTH(Sheet2!$AE2:$AE10000)=D2)+(MONTH(Sheet2!$AE2:$AE10000)=E3)))))

very cool, i see what you did there. Thanks again!

 

[Fluff]

My pleasure.