Adding together multiple COUNTIFS?

Michael Marques
edited 12/09/19 in Formulas and Functions

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!

Screenshot_2.png

Screenshot_1.png

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!