COUNTIFS not generating the correct output

Hello All,

I am trying to use this formula to COUNTIFS([Restaurant_Lounge]:[Restaurant_Lounge], >0, Status:Status, "Live", Vendor:Vendor, "VendorName") to get information from 3 different columns.

The Restaurant_Lounge column has blanks, numbers, and letters in it.

The Status:Status has a dropdown with various statuses.

Vendor:Vendor has a dropdown with various vendors in it.

What I am trying to do is get a sum of the numbers in the restaurant_lounge column, that are Live and has the VendorName specified.

I keep getting a very random number that I have no idea where it is coming from or even how it is being calculated.

I am new to this, so pretty much stumped and hoping for someone to shed some light on this one for me.

I appreciate any ideas you all may have.

Best Answer

  • RobertBuckley
    RobertBuckley ✭✭
    edited 09/02/22 Answer ✓

    OH @Mike TV I got it!! SUMIFS!! Not COUNTIFS! I did =SUMIFS(Restaurant_Lounge:Restaurant_Lounge, Status:Status, "Live", Vendor:Vendor, "Microsoft") and that got me my numbers I was looking for!!!

    Thank you for helping me thing through that one!

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @RobertBuckley

    If [Restaurant_Lounge] column has both letters and numbers in it then your criteria isn't going to work. It's trying to count the ones that only have a number in it and is greater than 0.

    You might try this:

    =COUNTIFS([Restaurant_Lounge]:[Restaurant_Lounge], NOT(ISBLANK(@cell), Status:Status, "Live", Vendor:Vendor, "VendorName")

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @RobertBuckley

    Also, if there are some that are Live and have the VendorName you're looking for but the Restaurant_Lounge cell is blank and you still want that counted then maybe you just need to remove the Restaurant_Lounge from the formula and only look to count rows which have Live and the VendorName you're looking for in them.

  • Hi Mike! Thank you for your reply. I tried the change you suggested but it came up with an invalid argument.

    So restaurant_lounge has multiple values in a row, so one may have a 1, one may have 3, etcetera. I am trying to count all that up, based on a hotel being Status being "Live" and the vendor being a specific vendor.

    So in the example below. If I set my vendor Microsoft I should get a total of 6 using this formula but I get 3.

    =COUNTIFS([Restaurant_Lounge]:[Restaurant_Lounge], >0, Status:Status, "Live", Vendor:Vendor, "Microsoft")

    Hope that clarifies things a little bit...seems it is counting the vendor and not the Restauraunt_Lounge column?

  • RobertBuckley
    RobertBuckley ✭✭
    edited 09/02/22 Answer ✓

    OH @Mike TV I got it!! SUMIFS!! Not COUNTIFS! I did =SUMIFS(Restaurant_Lounge:Restaurant_Lounge, Status:Status, "Live", Vendor:Vendor, "Microsoft") and that got me my numbers I was looking for!!!

    Thank you for helping me thing through that one!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!