Count occurrence if the any of the next columns have blank

[manish4993395]

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! :)

[#2]

@manish4993395 

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

[#3]

@manish4993395 

In H2:

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

Fill down.

[#4]

@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

[#5]

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

[#6]

Ok, but what about something like

Shopping Shopping NULL NULL

Would that return 1 or 2?

[#7 · Accepted solution]

@manish4993395 

This last change came as a bit of a surprise!  Up to there my solutions were the same as @Hans Vogelaar 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)
    )
  ))

[#8]

1