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

excel

Formulas and Functions

office 365

PeterBartholomew1

Sep 21, 2023

manish4993395

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

shopping.xlsx19 KB

manish4993395

Copper Contributor

Sep 21, 2023

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

JosWoolley

Iron Contributor

Sep 21, 2023

Ok, but what about something like

Shopping Shopping NULL NULL

Would that return 1 or 2?

manish4993395
Copper Contributor
Sep 21, 2023
1

Forum Discussion

Count occurrence if the any of the next columns have blank

Resources