countif to total summary two different values into one

Eriq ✭✭
edited 12/09/19 in Formulas and Functions

I have a sheet with different values I would like to sum a group of them and not in detail. 

Example I have values like CAR, BUS, TABLE, CHAIR

I am trying to use a countif formula to sum up a total off all vehicles, like CAR and BUS.

So I tried countifs(Range:range, CAR, Range:Range, BUS) 

however that doesnt seam to work. Any ideas?


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    The main reason this won't work is because the Countifs formula Is used to find cells/rows that match all the criteria... In your example formula. You are looking for any cell that has Bus in that range, AND has Car in that range. Because you won't ever have BUS and CAR in the same cell, the formula fails. The prime example for adding more criterion in your countifs would be if you had a separate column for color of object, and you wanted to count all yellow busses only... then you would use =countifs(objectcolumn:objectcolumn, "Bus", colorcolumn:colorcolumn, "Yellow") 

    All you need to do to get a count of all busses and cars in total is add separate COUNTIFS statements together to get your results. My example below has only cars and busses but you can use a simple plus sign between countifs to add additional objects like boats or planes to your count. laugh 

    =COUNTIFS([Column Title]:[Column Title], "Car") + COUNTIFS([Column Title]:[Column Title], "Bus")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!