Sum and Count multiple values in a range

SkiPatrolScott · 02/01/23

我去ing 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...

Mike TV · 02/01/23

@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 · 02/01/23

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

Mike TV · 02/01/23

@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 · 02/01/23

@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 · 02/02/23

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

SkiPatrolScott · 02/14/23

@Mike TVNot 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},[email protected], {Ava Route Route}, <>"AVA IC / Dispatch")

Mike TV · 02/14/23

@SkiPatrolScott

You can just keep adding to that formula like so:

=COUNTIFS({Ava Lead},[email protected], {Ava Route Route}, <>"AVA IC / Dispatch", {Ava Route Route}, <>"This", {Ava Route Route}, <>"That")

Sum and Count multiple values in a range

Best Answers

Answers

Categories