Helper Field, Display Data based on "null" or "blank" in other field..
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]
Answers
-
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
Hope that helps
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
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...
-
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
-
@SkiPatrolScott sorry for my delay I was out on the hills !!!
As you know you are using a multiple selections which presents its own issues.
I am thinking a COUNTM might work better here
HAS Searches for a specific value and COUNTM just counts the number of elements
You can then have a column simply have a COUNTM([Additional Patrolers]@row) and then pull that in do your record
@Paul Newcome had a discussion about it that might help
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
Hiya! 🙂
COUNTM can be very useful, yes! However are you looking to see if this specific person appears in a multi-select column, and how many times?
If so, we can use HAS for this.
Try:
=COUNTIFS({DP Log Add Patr}, HAS(@cell, Patroller@row), {Event}, [10-50]@row)
The reason you were getting Unparseable is because you had an extra parentheses after your first {range}.
Cheers,
Genevieve
