COUNTIFS not generating the correct output

RobertBuckley
RobertBuckley ✭✭✭
in Formulas and Functions

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.

· Share on FacebookShare on Twitter

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!

    · Share on FacebookShare on Twitter

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")

    · Share on FacebookShare on Twitter
  • 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.

    · Share on FacebookShare on Twitter
  • RobertBuckley
    RobertBuckley ✭✭✭
    https://community.smartsheet.com/discussion/comment/342164#Comment_342164

    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")

    image.png

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

    · Share on FacebookShare on Twitter
  • 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!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!