Countifs with multiple criteria

BrittanyC

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.

Paul Newcome

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?

BrittanyC

@Paul Newcome

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!

Matt Lynn ACT

@BrittanyC

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.

BrittanyC

@Matt Lynn-PCG

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.

Paul Newcome

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?

BrittanyC

@Paul Newcome

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!