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...
Best Answers
-
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.
-
Something like:
=COUNTIFS(Date:Date, @cell <= TODAY(), [Resource Request]:[Resource Request], OR(CONTAINS("Vacu", @cell), CONTAINS("Se", @cell), CONTAINS("Bac", @cell), CONTAINS("Tra", @cell))
Answers
-
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.
-
Something like:
=COUNTIFS(Date:Date, @cell <= TODAY(), [Resource Request]:[Resource Request], OR(CONTAINS("Vacu", @cell), CONTAINS("Se", @cell), CONTAINS("Bac", @cell), CONTAINS("Tra", @cell))
-
Thanks @Mike TV, I was hoping it was something easy, I keep mixing up orders of OR, AND, HAS....
-
@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")
-
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")
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives