Forum Discussion

mgaut341's avatar
mgaut341
Icon for Helper II rankHelper II
1 year ago
Solved

Counting blank fields based on another column

I am trying to count how many blank cells I have in my 'status' column when my 'appt date/time' field has a value in the cell. I have tried the following formula CALCULATE(COUNT('CCHHS Imaging & Pr...
  • mgaut341's avatar
    mgaut341
    1 year ago

    I was not able to get it to work, so I used a subtraction formula from the other result status' to get my correct answer. 

techies's avatar
techies
Icon for Super User rankSuper User
1 year ago

Hi mgaut341 please try this

 

Blank Status with Appt =
CALCULATE(
    COUNTROWS('Sheet4'),
    NOT(ISBLANK('Sheet4'[Appt Date/Time])),
    ISBLANK('Sheet4'[Status])
)
mgaut341's avatar
mgaut341
Icon for Helper II rankHelper II
1 year ago

That shows up as 0, not the 77 I am looking for

  • techies's avatar
    techies
    Icon for Super User rankSuper User
    1 year ago

    ok, create this calculated column to see the field is blank

     

    Status Debug =
    IF(
    ISBLANK('Sheet4'[Status]),
    "True Blank",
    "[" & 'Sheet4'[Status] & "] LEN=" & LEN('Sheet4'[Status])
    )

    • mgaut341's avatar
      mgaut341
      Icon for Helper II rankHelper II
      1 year ago

      This is my result 

       

      • techies's avatar
        techies
        Icon for Super User rankSuper User
        1 year ago

        ok please try this

         

        Blank Status with Appt =
        CALCULATE(
            COUNTROWS('Sheet4'),
            FILTER(
                'Sheet4',
                NOT(ISBLANK('Sheet4'[Appt Date/Time])) &&
                LEN(TRIM('Sheet4'[Status])) = 0
            )
        )