COUNTIFS not generating the correct output

RobertBuckley

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.

RobertBuckley

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!

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

@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.

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

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

RobertBuckley

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!