Count Unique Value with Multiple Criteria

[RattlingCarp3048]

I am trying to count unique values sheet "Daily Data Entry" column A if the following criteria are met:

Daily Data Entry Audit'!F:F = 'Monthly Data Audit'!B5
'Daily Data Entry Audit'!E:E = 'Monthly Data Audit'!A5
'Daily Data Entry Audit'!I:I = LookUp!$A$2

this is the formula i have so far. it is returning 0 when it should be returning 2
=IF(AND('Daily Data Entry Audit'!F:F='Monthly Data Audit'!B5,'Daily Data Entry Audit'!E:E='Monthly Data Audit'!A5,'Daily Data Entry Audit'!I:I=LookUp!$A$2),COUNTA(UNIQUE('Daily Data Entry Audit'!A:A)),"0")

I have already checked cell formatting for extra spaces and typos. What am i doing wrong?

 

[Fluff]

How about

=ROWS(UNIQUE(FILTER('Daily Data Entry Audit'!A:A,('Daily Data Entry Audit'!F:F='Monthly Data Audit'!B5)*('Daily Data Entry Audit'!E:E='Monthly Data Audit'!A5)*('Daily Data Entry Audit'!I:I=LookUp!$A$2))))

 

[RattlingCarp3048]

How about

=ROWS(UNIQUE(FILTER('Daily Data Entry Audit'!A:A,('Daily Data Entry Audit'!F:F='Monthly Data Audit'!B5)*('Daily Data Entry Audit'!E:E='Monthly Data Audit'!A5)*('Daily Data Entry Audit'!I:I=LookUp!$A$2))))

this worked. Thanks Fluff

 

[Fluff]

You're welcome & thanks for the feedback.

 

[RattlingCarp3048]

You're welcome & thanks for the feedback.

question: i finished adding in all the live data (over 1000 rows) and the formula worked great. I needed to narrow the calculation so i added an additional criteria (which works) but its returning #CALC when the value should be 0 as there is nothing to calculate. Is there a work around to make it show 0?

=ROWS(FILTER('Daily Data Entry Audit'!A:A,('Daily Data Entry Audit'!F:F='Monthly Data Audit'!B5)*('Daily Data Entry Audit'!E:E='Monthly Data Audit'!A5)*('Daily Data Entry Audit'!I:I=LookUp!$A$2)*('Daily Data Entry Audit'!J:J<>"")))

 

[Fluff]

You just need to wrap the formula in the IFERROR function.

 

[RattlingCarp3048]

You just need to wrap the formula in the IFERROR function.

thanks. That was one of the first things i tried yesterday with no luck, among several other options. guess i typed it wrong cut its working for me today.

 

[Fluff]

Glad it's sorted & thanks for the feedback.

 

[RattlingCarp3048]

Glad it's sorted & thanks for the feedback.

Fluff, sorry to keep coming back if i need to create a new thread i will but i think my issue links back to this formula...

there are approx. 1300 rows currently in this file and it will eventually grow to 10,000-20,000 rows before retiring it. While the formula you provided is currently working beautifully, i have noticed a significant decrease in calculation/processing speeds (around 2 minutes just to save and 30-60 seconds between simple clicks between cells). When i turn off the auto-calculations the file runs extremely smooth, however, its not ideal to do this for the entire workbook long term. These problems didn't exist prior to the formula being added and its only this file so i know its not the computer and i have already ruled out internet connection speeds. Could it be that the formula is too volatile and slowing it down? Maybe there is another combination of functions less harsh that we could use? is there a way to turn off the auto-calculations to the sheet specifically and not the entire workbook?

i'm not sure where to take it from here.

 

[Fluff]

Just change the ranges to something suitable rather than using whole columns.