Helper Field, Display Data based on "null" or "blank" in other field..

SkiPatrolScott · 02/02/23

Have been racking my small brain on how to create this Helper Field.

This is a SHEET that is pulling data from our daily log. I am not sure if I can create the formula here or if I have to go to the LOG SHEET. Right now its counting all dispatched "10-50" which I still want, but if we get a false "10-50", in ROW [NEG-IRF] we put the reason. So I also need a new Field that will represent [Actual 10-50] which would essentially be a [Event]="10-50" as long as [NEG-IRF] is BLANK.

I'm pretty sure I can add the new [Column] that will count [actual 10-50]

Brent Wilson · 02/02/23

I think this is what you are asking.

=IF(AND(Event]@Row="10-50",ISBLANK([Neg-IRF])),"10-50","")

You could also use a check for blanks in the [NEG-IRF] in the COUNTIFS

Something like. I didn't test

=COUNTIFS({BuletPatroller1},Patroller@row,{Event},[10-50]@row,[NEG-IRF]@row,<>"")

Read this for some info on blanks in COUNTIFS

https://community.smartsheet.com/discussion/70187/help-w-countifs-and-isblank-and-not-isblank

Hope that helps

SkiPatrolScott · 02/03/23

Thanks @Brent Wilson I did have to modify the formula slightly, I know it seems redundant to display an entry already entered but we wanted to capture the initial response even though it changed while en route. On the totals SHEET I just used the same tabulation formula on the confirmed field...

SkiPatrolScott · 02/05/23

@Brent Wilson , @Genevieve P.

Hey there, hoping I am just missing how to input "HAS" . In the formula above, from the main SHEET, we have a [Bullet Patroller] (which this formula has calculated the sought out number) pic1, and we have [Additional Patrollers].

The [Bullet Patroller] is a single entry field. The [Additional Patrollers] is a multiple entry field, in that we can have one to many Patrollers added for an inc. The LIST that SS pulls from are the same so the names should be the same.

I would like to calculate (sum or count) a [Additional Total] field that gives me the total for the Patroller when they are listed in the [Additional Patrollers] field for a "10-50" in the [Event] pic2. Currently getting the dreaded UNPARSEABLE