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.

这个表是把数据从我们的细节y 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},[email protected],{Event},[10-50]@row,[NEG-IRF]@row,<>"")

Read this for some info on blanks in COUNTIFS

Hi team - I have 2 columns (Seat and Open Req) that I need to count:<\/p>

When Seat = Filled and Open Req is not blank<\/p>

When Seat = Filled and Open Req is blank<\/p>

I'm struggling with the COUNTIFS sequence to get this to work.<\/p>

Any help would be greatly appreciated.<\/p>","bodyRaw":"[{\"insert\":\"Hi team - I have 2 columns (Seat and Open Req) that I need to count:\\nWhen Seat = Filled and Open Req is not blank\\nWhen Seat = Filled and Open Req is blank\\nI'm struggling with the COUNTIFS sequence to get this to work.\\nAny help would be greatly appreciated.\\n\"}]","format":"rich","dateInserted":"2020-07-28T15:42:59+00:00","insertUser":{"userID":118864,"name":"jmo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g0lA5zVbOLw!-wfAd_lcHz4!voKaS5U1cZv","dateLastActive":"2023-01-17T16:39:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/70187\/help-w-countifs-and-isblank-and-not-isblank","embedType":"quote","name":"Help w\/ COUNTIFS and ISBLANK and NOT ISBLANK"}"> https://community.smartsheet.com/discussion/70187/help-w-countifs-and-isblank-and-not-isblank

Hope that helps

SkiPatrolScott · 02/03/23

Thanks@Brent WilsonI 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].

的[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

Brent Wilson · 02/05/23

@SkiPatrolScottsorry 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

https://help.smartsheet.com/function/countm

https://help.smartsheet.com/function/has

@Paul Newcomehad a discussion about it that might help

My data input sheet has several columns that are Multi-select dropdowns. For my Metrics summary page, I am struggling with COUNTIF when the user has selected multiple options.<\/p>\n

Example of options in the Multi-selection dropdown:<\/p>\n

Apple<\/li>\n
Pear<\/li>\n
Grapes<\/li>\n
Banana<\/li>\n
Other<\/li>\n<\/ul>
I need guidance on how to structure the criteria for when multiple options are chosen, such as Apple & Pear. I want Apple & Pear<\/strong> to be a distinct count, not count all the times Apple or Pear are used individually in the range. <\/p>\n
Using the data set shown, I should get the following totals:<\/p>\n
- Apple - 1 (Not 4, which is the total within the range)<\/em><\/li>\n
- Pear - 1 (Not 3, which is the total within the range)<\/em><\/li>\n
- Grapes - 2<\/li>\n
- Other - 1<\/li>\n
- Apple Pear - 2<\/li>\n
- Apple Banana - 1<\/li>\n<\/ul>
  $\"Multi-Select.JPG\"$ <\/img><\/p>","bodyRaw":"
  My data input sheet has several columns that are Multi-select dropdowns. For my Metrics summary page, I am struggling with COUNTIF when the user has selected multiple options.<\/p>\n\n
  Example of options in the Multi-selection dropdown:<\/p>\n\n
  \n\t
  Apple<\/li>\n\t
  Pear<\/li>\n\t
  Grapes<\/li>\n\t
  Banana<\/li>\n\t
  Other<\/li>\n<\/ul>\n\n
  I need guidance on how to structure the criteria for when multiple options are chosen, such as Apple & Pear. I want Apple & Pear<\/strong> to be a distinct count, not count all the times Apple or Pear are used individually in the range. <\/p>\n\n
  Using the data set shown, I should get the following totals:<\/p>\n\n
  \n\t
  Apple - 1 (Not 4, which is the total within the range)<\/em><\/li>\n\t
  Pear - 1 (Not 3, which is the total within the range)<\/em><\/li>\n\t
  Grapes - 2<\/li>\n\t
  Other - 1<\/li>\n\t
  Apple Pear - 2<\/li>\n\t
  Apple Banana - 1<\/li>\n<\/ul>\n
  $\"Multi-Select.JPG\"$ <\/p>","format":"html","dateInserted":"2019-12-03T16:24:12+00:00","insertUser":{"userID":113161,"name":"MJayMay","url":"https:\/\/community.smartsheet.com\/profile\/MJayMay","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-12\/ee\/8d\/nee8da44d5a6246e85b7798a17af17d91.png","dateLastActive":"2020-09-25T20:27:44+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/64336\/countif-multi-selection-dropdown-count-multiple-entries","embedType":"quote","name":"COUNTIF & Multi-Selection Dropdown -- Count multiple entries"}"> https://community.smartsheet.com/discussion/64336/countif-multi-selection-dropdown-count-multiple-entries