Nested COUNTIFS
I'm trying to count the matching items in two cells to a reference in another sheet. Individually they work with COUNTIF and produce the associated result but then I want to test both it comes back with "0".
=COUNTIF({DNAC Adoption Range 1}, Partner106) = counts the correct partner name
=COUNTIF({DNAC Adoption Range 1}, Solution106) - counts the correct # solution name
=COUNTIFS({DNAC Adoption Range 1}, Operation106, {DNAC Adoption Range 1}, Partner106) - returns "0" but should return "1"
What am I missing?
Best Answers
-
The 1st 2 examples both return the correct number of 1 instance for both partner106 and solution106 resepctively. The 3rd example tests for both comes back as 0 when it too should be 1 as there are valid records where both test match.
-
You would use something like this...
=COUNTIFS(Partner:Partner, "IBM", Operation:Operation, "South")
Answers
-
Difficult to say exactly without seeing your Sheets in question, but it looks like your COUNTIFS (last formula) will only be 0 if the values in the Operation and Partner columns are the same, is this how you intend it to work? This is because your formula is going to count only rows in the DNAC Adoption Range 1 that have a value equal to Partner106 and Solution106. Individually (the first two formulas) will count rows where the specified column has the correct value irrespective of the other column.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
The 1st 2 examples both return the correct number of 1 instance for both partner106 and solution106 resepctively. The 3rd example tests for both comes back as 0 when it too should be 1 as there are valid records where both test match.
-
I've also tried this with COUNTIF(AND... but it won't parse.
=COUNTIF(AND{DNAC Adoption Range 1}, Partner106, {DNAC Adoption Range 1}, Operation106)
-
Are you able to provide a screenshot of the data that should be matching and what it should be matching to?
-
I think I've found the issue but not sure how to make it work. There are multiple instances of both Partner106 & Operation 106. It's matching the 1st instance and returning 0 because it doesn't match.
In excel I used to count the number of instances then have it loop through the list to test the 2nd variable. Can I do that in SS?
Partner. Operation
IBM South
IBM Central
IBM. East
IBM West
IBM South
How would I get it to match Partner(IBM) and Operation(South) and return a result of 2?
Thanks
-
You would use something like this...
=COUNTIFS(Partner:Partner, "IBM", Operation:Operation, "South")
-
Hi Paul, That works when all the data is in the same sheet but not for referenced sheets. It did lead me down another path though. When referencing another sheet we can't do Partner1:Partner300 in the formula so I created a separate reference range for each of the tests and only selected the row with the corresponding data.
Effectively the 1st test only selected the partners who match the partner test then the operations test only matched / counted what it matched which is the correct answer.
Here's what the formula finally looked like when referencing another sheet:
=COUNTIFS({DNAC Adoption Range 3}, Partner1, {DNAC Adoption Range 4}, Operation1)
Thanks for everyone's guidance!
-
Yes. Referencing another sheet is definitely going to look different, but using the example I provided made it easier to show which columns to select along with the syntax.
As for selecting a specific range of cells... You CAN do that in cross sheet references. You just highlight that particular range instead of clicking on the column header to select the entire column.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62K Get Help
- 338 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.3K Ideas & Feature Requests
- 130 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!