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))
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.