I have three columns shown in a file:
Column B is a landowner
Column M is the agent who was assigned the landowner
Column N is the status of the assignment
I want to do a count formula to see how many unique landowners the agent has "signed". When I filter my sheet called "Master", I can see that only 1 agent has signed:
So when I go to my formula, it still shows a 0 for Kevin, but there should be a 1:
I have the -1 at the end of the formula to omit the header row in the count. When I take that off, everyone gets a 1 in the formula when that isn’t accurate.
Formula:
=COUNTA(UNIQUE(FILTER(Master!$B:$B,(Master!$M:$M=Metrics!$A17)*
(Master!$N:$N="Signed"))))-1
>Solution :
Instead of using COUNTA() function use SUM() with an error check as well. That said, you can amend the formulas as follows:
- Filter the data by conditions:
=FILTER(Master!$B:$B,(Master!$M:$M=Metrics!$A17)*(Master!$N:$N="Signed"))
- Get Unique Values
=UNIQUE(FILTER(Master!$B:$B,(Master!$M:$M=Metrics!$A17)*(Master!$N:$N="Signed")))
- Get the counts to check whether there is no blanks assumes there are no spaces here:
=SUM(--(UNIQUE(FILTER(Master!$B:$B,(Master!$M:$M=Metrics!$A17)*(Master!$N:$N="Signed")))<>""))