Sum and Count multiple values in a range

I am going to need to count the number of values that are listed in a row/column. Right now I can exclude but I want to be able to list just part of the value and tabulate all those values

=COUNTIFS(Date:Date, @cell <= TODAY(), [Resource Request]:[Resource Request], CONTAINS("Vacu", @cell))

This is the current formula, and I get back all values that include Vacu in the name, so I get Vacuum Splint and Vacuum Mattress, which is what I want. But I want to create a formula that will also find "Se", "Bac","Tra" and bring back; Seat, Backboard, Trauma Pack, so the above formula will go from 15 to 25 with the added search. All of these are Resource Request Options, and many can be in the same cell as it is a multi values option.

I did look but could not find any options that worked...

Find more posts tagged with

Accepted answers

Mike TV

@SkiPatrolScott

I think you just need to add an OR function to the front of the CONTAINS and after the first CONTAINS add another CONTAINS for the next thing, then another for the next, etc.

Mike TV

@SkiPatrolScott

Something like:

=COUNTIFS(Date:Date, @cell <= TODAY(), [Resource Request]:[Resource Request], OR(CONTAINS("Vacu", @cell), CONTAINS("Se", @cell), CONTAINS("Bac", @cell), CONTAINS("Tra", @cell))

All comments

Mike TV

@SkiPatrolScott

I think you just need to add an OR function to the front of the CONTAINS and after the first CONTAINS add another CONTAINS for the next thing, then another for the next, etc.

Mike TV

@SkiPatrolScott

Something like:

=COUNTIFS(Date:Date, @cell <= TODAY(), [Resource Request]:[Resource Request], OR(CONTAINS("Vacu", @cell), CONTAINS("Se", @cell), CONTAINS("Bac", @cell), CONTAINS("Tra", @cell))

SkiPatrolScott

Thanks @Mike TV, I was hoping it was something easy, I keep mixing up orders of OR, AND, HAS....

SkiPatrolScott

@Mike TV Not sure what I am doing wrong, can you help? The below formula is working to eliminate "AVA IC / Dispatch" but I need to add additional "values"... to not count. They will all be from {Ava Route Route} column, probably at least two or three... even better if it could be a contains options as there are multiple that start with the same letters.. "GAZ"

=COUNTIFS({Ava Lead}, Patroller@row, {Ava Route Route}, <>"AVA IC / Dispatch")

Mike TV

@SkiPatrolScott

You can just keep adding to that formula like so:

=COUNTIFS({Ava Lead}, Patroller@row, {Ava Route Route}, <>"AVA IC / Dispatch", {Ava Route Route}, <>"This", {Ava Route Route}, <>"That")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.