Remove duplicates from countifs statement based on another column's values

[byrdbrain22]

Hello,

I'm looking to accurately countifs a long series of data and remove the duplicates from that series based on another column's values. It seems like it shouldn't be that hard but haven't been able to find a solution. My data set has 10,000 rows, so I am not looking for a pivot table solution. Image attached with sample data set. Current formula in B3 is the following: =COUNTIFS($B$9:$B$15,$A3,$C$9:$C$15,B$2), it is returning 2 residential meters but there is a duplicate reading in the data set (row 9 and row 14). I want the answer to be only 1 in B3. How do I remove that duplicate based on the column A, which is the meter number?
I have tried combinations of sum, frequency, and ifs such as SUM(IF(COUNTIFS($B$12:$B$18,$B3,$C$12:$C$18,C$2),FREQUENCY(A12:A18,A12:A18)>0,1)).

Thank you for your help!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=ROWS(UNIQUE(FILTER(A2:A15,($B$9:$B$15=$A3)*($C$9:$C$15=B$2))))

 

[byrdbrain22]

Thank you! That worked! The only error I am getting is #CALC! when there are no values to count in the array for the filter function, so I just had to add the "if empty" condition. Final formula reads like this:=ROWS(UNIQUE(FILTER($A$9:$A$18,($B$9:$B$18=$A3)*($C$9:$C$18=B$2),0)))

THANK YOU SO MUCH

 

[byrdbrain22]

Thank you! That worked! The only error I am getting is #CALC! when there are no values to count in the array for the filter function, so I just had to add the "if empty" condition. Final formula reads like this:=ROWS(UNIQUE(FILTER($A$9:$A$18,($B$9:$B$18=$A3)*($C$9:$C$18=B$2),0)))

THANK YOU SO MUCH

Sorry, one more question. So the "If empty" condition on the filter function returns a value if there is not a solution. However, if the option doesn't exist at all then it still counts "1". For example, if there are no landscape 2-inch meters, it still returns a "1" value in D5. Which is not correct, if it doesn't exist at all. What do you think?

 

[Fluff]

As you have discovered, you cannot use the If Empty condition. If col A are numeric values you can use Count instead of Rows, otherwise you need to use Iferror

=IFERROR(ROWS(UNIQUE(FILTER(A9:A15,($B$9:$B$15=$A3)*($C$9:$C$15=B$2)))),0)

 

[byrdbrain22]

The "count" instead of "rows" worked. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.