Adding together multiple COUNTIFS?

Michael Marques

Hey everyone. I'm trying to implement a formula that will allow me to reference two ranges from one sheet and follow the specified criteria that is given to them.

My most recent attempt;

=COUNTIFS({Unit_Type}, "CP2 - Double", {ACCS Overall}, [T2]26) + COUNTIFS({Unit_Type}, "CP4 - Double", {ACCS Overall}, [T2]26) + COUNTIFS({Unit_Type}, "CP4-SP - Double", {ACCS Overall}, [T2]26) + COUNTIFS({Unit_Type}, "KP1S - Double", {ACCS Overall}, [T2]26)

Unfortunately, this comes up as #INCORRECT ARGUMENT SET

What goal I want to accomplish;

I need to add together all the ACCS being used based on certain criteria (for example: add together all Unit Types that are using a SVA025-C-L, but only if they are one of the following: CP2 - Double, CP4 - Double, CP4-SP - Double, or KP1S - Double).

Formula: SVA025-C-L (CP2 - Double) + SVA025-C-L (CP4 - Double) + SVA025-C-L (CP4-SP - Double) + SVA025-C-L (KP1S - Double)

Steps to accomplish said goal;

1. Setup the ranges I will need to utilize in the first sheet (Double Camera System) from the other sheet (Cameras). The two ranges I'll be using are Unit_Type and ACCS Overall.
2. Define the criteria for both ranges:
   1. Unit_Type is searching for CP2 - Double, CP4 - Double, CP4-SP - Double, and KP1S - Double.
   2. ACCS Overall is searching for SVA025-C-L
3. Add together the remaining values after searching through both ranges and criteria.
4. Set # of ACCS Cameras Used's value to final sum.

Hopefully I'm not over complicating this, but any advice would be helpful!

Paul Newcome

Try something along the lines of...

 

=COUNTIFS({Accs Overall}, [T2]26, {Unit Type}, OR(@cell = "CP2 - Double", @cell = "CP4 - Double", @cell = "CP4 - SP - Double", "KP1s - Double"))

.

Are you using the proper steps to reference the other sheet?

jay.rathbun

Hi Michael, 

I was facing the same issue you have listed above. My workaround for this was to do a SUM function and then have each count if be the value. In your case it would look something like:

=SUM(COUNTIFS({Unit_Type}, "CP2 - Double", {ACCS Overall}, [T2]26), COUNTIFS({Unit_Type}, "CP4 - Double", {ACCS Overall}, [T2]26), COUNTIFS({Unit_Type}, "CP4-SP - Double", {ACCS Overall}, [T2]26), COUNTIFS({Unit_Type}, "KP1S - Double", {ACCS Overall}, [T2]26))

 

Hope this helps!

Jay