Forum Discussion
Solved
Count distinct values based on 2 criteria in other columns
Hi all, First post here, tried to find my answers, but didn't get a complete solution for my issue, so hope you can help. I have to count no of distinct trial numbers in a range in column A ...
Based on initial formula that could be
=SUM( ($Z3:$Z15<>"")* ISNUMBER( SEARCH("leaf",$D3:$D15)) * IFERROR( 1 /COUNTIFS($A3:$A15,$A3:$A15, $Z3:$Z15,"<>",$D3:$D15,"LEAF*"),1))
Try
=LET(f, FILTER(A3:A15, (LOWER(LEFT(D3:D15, 4))="leaf")*(Z3:Z15<>""), ""), IF(f="", 0, COUNTA(UNIQUE(f))))
That gives me the error #spill!
Based on initial formula that could be
=SUM( ($Z3:$Z15<>"")* ISNUMBER( SEARCH("leaf",$D3:$D15)) * IFERROR( 1 /COUNTIFS($A3:$A15,$A3:$A15, $Z3:$Z15,"<>",$D3:$D15,"LEAF*"),1))- Thank you Sergei, this indeed works as well, seems many ways lead to the same solution!
Yes, practically everything in Excel could be done by several ways. It's infinite.
Hopefully there will always be at least one.
=LET(f, FILTER(A3:A15, (LOWER(LEFT(D3:D15, 4))="leaf")*(Z3:Z15<>""), ""), COUNTA(UNIQUE(f)))
- Yes, this works! And if I would want to do the same but for Plant being mentioned in Column D what would I need to change?
I've now done it this way =LET(f,FILTER($A3:$A15,(LOWER(LEFT($D3:$D15,5))="Plant")*(J3:J15<>""), ""),COUNTA(UNIQUE(f))) and that seems to work, but maybe it could be done differently?That should be
=LET(f,FILTER($A3:$A15,(LOWER(LEFT($D3:$D15,5))="plant")*(J3:J15<>""), ""),COUNTA(UNIQUE(f)))
with lower case "p" in "plant"