SOLVED

# Count occurrence if the any of the next columns have blank

Copper Contributor

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

7 Replies

# Re: Count occurrence if the any of the next columns have blank

``````=BYROW(
C2:G8,
LAMBDA(Φ,
IFERROR(N(MMULT(XMATCH({"Shopping","NULL"},Φ,,{1,-1}),{-1;1})>0),0)
)
)``````

# Re: Count occurrence if the any of the next columns have blank

In H2:

=IFERROR(--(XMATCH("NULL",C2:G2,,-1)>XMATCH("Shopping",C2:G2)),0)

Fill down.

# Re: Count occurrence if the any of the next columns have blank

@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

# Re: Count occurrence if the any of the next columns have blank

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

# Re: Count occurrence if the any of the next columns have blank

Ok, but what about something like

Shopping Shopping NULL NULL

Would that return 1 or 2?
best response confirmed by manish4993395 (Copper Contributor)
Solution

# Re: Count occurrence if the any of the next columns have blank

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

# Re: Count occurrence if the any of the next columns have blank

1
1 best response

Accepted Solutions
best response confirmed by manish4993395 (Copper Contributor)
Solution

# Re: Count occurrence if the any of the next columns have blank

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