Forum Discussion
Solved
Count occurrence if the any of the next columns have blank
Hi Experts,
I want to count the 'Shopping' for every row if any next column is having a NULL value. If there is no NULL value column then it should not be counted. Please take the 'result' column as desired output.
Thanks in advance! 🙂
This last change came as a bit of a surprise! Up to there my solutions were the same as HansVogelaar and JosWoolley. To count occurrences I changed the formula to
Worksheet formula = BYROW(table, CountBeforeλ("Shopping", "NULL")) CountBeforeλ = LAMBDA(countString, untilString, LAMBDA(line, LET( end, XMATCH(untilString, line, , -1), matches, TAKE(line, , end) = countString, IFERROR(SUM(SIGN(matches)), 0) ) ))
- the logic should be like if there are two 'Shopping' in first row with NULL in any coming column then also it should be counted. Please take the example of first row:
Level1 Level2 Level3 Level4 Level5 result
Shopping Resolve NULL Shopping NULL 2
Shopping Ammendment Resolve NULL NULL 1
A Shopping Ammendment NULL Shopping 1
B NULL Shopping Ammendment Resolve 0
A NULL NULL NULL Shopping 0- Ok, but what about something like
Shopping Shopping NULL NULL
Would that return 1 or 2?
=BYROW( C2:G8, LAMBDA(Φ, IFERROR(N(MMULT(XMATCH({"Shopping","NULL"},Φ,,{1,-1}),{-1;1})>0),0) ) )JosWoolley the logic should be like if there are two 'Shopping' in first row with NULL in any coming column then also it should be counted. Please take the example of first row:
Level1 Level2 Level3 Level4 Level5 result Shopping Resolve NULL Shopping NULL 2 Shopping Ammendment Resolve NULL NULL 1 A Shopping Ammendment NULL Shopping 1 B NULL Shopping Ammendment Resolve 0 A NULL NULL NULL Shopping 0 This last change came as a bit of a surprise! Up to there my solutions were the same as HansVogelaar and JosWoolley. To count occurrences I changed the formula to
Worksheet formula = BYROW(table, CountBeforeλ("Shopping", "NULL")) CountBeforeλ = LAMBDA(countString, untilString, LAMBDA(line, LET( end, XMATCH(untilString, line, , -1), matches, TAKE(line, , end) = countString, IFERROR(SUM(SIGN(matches)), 0) ) ))
