Adding together multiple COUNTIFS?

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

  • Paul Newcome
    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?

  • 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

     

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!