Help on formula: count if with multiple criteria

SHOOD

@Paul Newcome

Hi Paul!

I'm not sure I know exactly what you are asking. Are you asking if #NO MATCH is present in the Workset column? I was surprised to see when Filtering that #NO MATCH was an option in the Workset column and when I remove all other filters, there are 34 rows where Workset = #NO MATCH.

SHOOD

@Paul Newcome

Hi Paul,

I delete the rows in my smartsheet with #NO MATCH and now my countifs are working perfectly.

Thanks for heading my down the right path!

Susan

Paul Newcome

@SHOOD Happy to help. 👍️

Another option would have been to wrap whatever formula is causing that error in the Workset column in an IFERROR function. This way you could keep the rows but remove the error.

=IFERROR(original_formula, "")

Mark Wood

Hi,

I'm sure the answer is already in this thread but I cannot make this work. I am trying to count attribution of work from a smartsheet in the last X days to build a dashboard. I have used

=COUNTIFS(({Sales Support Booking Requests Range 1}, "Tara Hill"),AND({Sales Support Booking Requests Range 2},<6))

COUNTIF ... "Tara Hill" works and COUNTIF... ,<6 works but combing the two does not want to happen

Whats the obvious bit I've missed? Thank you!

Paul Newcome

@Mark Wood Your syntax is incorrect.

=COUNTIFS({Sales Support Booking Requests Range 1}, "Tara Hill", {Sales Support Booking Requests Range 2}, @cell < 6)

alibick

@Paul Newcome seems to be the expert I need. I feel like I'm inches away from this one, but cannot get anything other than #unparsable or #incorrectargumentset no matter what example I try to follow. I've got a range of shows (ShowID_ref; one column) that has unique identifier for different types of shows and a range of songs (Song_ref; multiple columns) that contain songs played at each show.

I'm looking to count the number of times a song was played across all shows of a certain type. Each half of the equation works on its own, but as soon as I combine them in a CountIfs, it breaks. (The ShowID and the Song List may have extra characters associated with them which is why I'm trying to use CONTAINS.). This formula works in Excel with a * but I'm a Smartsheet newbie and can't get it to work.

=COUNTIFS({ShowID_ref}, CONTAINS("Band4",@cell), [{Song_ref}, CONTAINS("Celebrity",@cell)])

Example data in ShowID Column: Band4-001

Example data in Song Columns: Celebrity (acoustic)

This particular attempt returns #unparsable. I'd also love to replace the use of "Celebrity" with [Song Name]@row so that I can just make it a Column formula.

Thank you!!

Paul Newcome

@alibick The square brackets before {Song_ref} and after the CONTAINS function should be removed.

Austin McLaren

Hi @Paul Newcome, I am hoping to can help. I am responsible for a subset of projects in an otherwise large portfolio, thus I am trying to create metrics using multiple criteria. I receive Uparseable using the following COUNTIFS:

=COUNTIFS([COE Service Owner]:[COE Service Owner], "Keith Donaldson", Status:Status, "Active",) [Business Segment]:[Business Segment], OR(@cell = "Store Operations", @cell = "OMNI-Channel", @cell = "Product", @cell = "New Revenue Streams")

I am looking for an accurate count of projects that meet the above criteria: COE Service Owner, for Active projects across multiple business segments listed above. Where did I go wrong? I know the formula works when limited to one business segment but not multiple.

If fixable, how could I then further refine to get a count of projects that meet the above criteria across the following project phases: Investigate, Analysis, Design, Development, Implement? Any help would be greatly appreciated.

Thank you

Paul Newcome

Try moving the closing parenthesis after "Active", to the end of the formula.

Austin McLaren

Thank you @Paul Newcome, that corrected my issue! Follow up question, how can I add to this Countifs formula to count these projects in a particular phase? For example, of these 7 projects identified in my corrected Countifs formula, how may are in Phase:Phase, "Design"? Thanks again for your help!

Paul Newcome

@Austin McLaren You would add another range/criteria set the same way you have the other range/criteria sets.

=COUNTIFS(..........................................., Phase:Phase, "Design")

Megan Doyle

@Paul Newcome Apparently you are the guru! I have tried following the above directions but I cannot seem to get my formula to work. I am trying to calculate 2 columns, the number of spaces selected, by each date selected. Original sheet screenshot below.

I got the first part of the calculation to work below, but it it just totaling the cells.

=COUNTIFS({Access Space Request}, CONTAINS("Double Villa", @cell))

Now I need to have those totals split into each date so I can see the total amount of people that will be in each space on each day. Below is what I have tried so far but I keep getting #unparseable

=COUNTIFS({Access Space Request}, CONTAINS("18th Green Marquee" @cell), {Date Request}, CONTAINS("08/09/22" @cell))

Thank you in advance!

Paul Newcome

@Megan Doyle It looks like you just forgot your commas between the criteria and @cell references in both of your CONTAINS functions.

Megan Doyle

@Paul Newcome Now it says #incorrect argument set. Is there something I need to put between the 2 criteria such as "AND" or a plus or something?

=COUNTIFS({Access Space Request}, CONTAINS("18th Green Marquee", @cell), {Date Request}, CONTAINS("08/09/22", @cell))

Paul Newcome

@Megan Doyle No. Your syntax is correct. Double check both ranges to ensure there isn't a cell tucked away somewhere that has an error in it.

There have also been reports of issues with Cross Sheet references in general. Typically it seems as if VLOOKUP and INDEX functions are what have been impacted, but others have been reporting issues with other functions as well.

If there are no issues with the ranges, then lets try the "turn it off and then on again" approach.

Remove the formula completely from the sheet.

Sign out of Smartsheet.

Clear your browser's cookies and cache.

Sign back into Smartsheet.

Manually reenter the formula (no copy/paste).