Countifs with multiple criteria
Is it possible to use Countifs with multiple criteria when one of the criteria is listed in the column multiple times? On Sheet1, I want to count if there is"Yes" in the Phone column {SRR Training Checklist Range 1} (from Sheet 2) that matches the name on Sheet1 in Assignments3- also cross referenced as {SRR Training Checklist Range 3} from Sheet2. As you can see on Sheet2, the name can be listed multiple times which is throwing off the formula.
Best Answers
-
It doesn't have anything to do with how many times the name is listed in the source sheet. What do you get if you use a COUNTIFS for just the name?
-
Thank you for pointing this out! I was able to figure out that the name listed multiple times had a space at the end, which obviously was throwing off the formula. Thank you!
Answers
-
Yes you can do multiple criteria with countifs. In your example it would count how many times that occurs and from the screenshot I see 4 matches. If you're just looking for a yes or no type situation you could say something like
if( [insert your formula] > 0, 1,0)
This would count it and if the result was more than 0, input a 1. I feel like I'm misunderstanding what you're looking for here.
-
Thank you. I am trying to do either of your suggestions; however, because the highlighted name in the SRR column is listed more than once, the formula results in 0 or blank. When I test the formula for the other name that is listed once (Lavontae Rogers) it works. So my conclusion is because the name is listed multiple times it is throwing off the formula. I am wondering if there is a way to get around that by adding something to the formula or using an alternate formula.
-
It doesn't have anything to do with how many times the name is listed in the source sheet. What do you get if you use a COUNTIFS for just the name?
-
Thank you for pointing this out! I was able to figure out that the name listed multiple times had a space at the end, which obviously was throwing off the formula. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!