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

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
        )
    )
    • mgaut341's avatar
      mgaut341
      Icon for Helper II rankHelper II
      1 year ago

      Sadly that doesn't give me the correct answer

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

        ok, what it's currently shwing?