Forum Discussion
COUNTIFS Help
Recruiter here - tracking the status of the jobs currently being recruited on. I want to count all of the jobs for a company in a certain status.
I checked for spaces and spelling - so I can't figure out what to do.
=countifs(Danielle!A:A, "Interplastic", Danielle!M:M, "Resume Intake/Review","Screening","Manager Interview","Sourcing","Agency Search").
I know it can be done - HELP!
Found my solve = =SUM(COUNTIFS(Danielle!A:A, "IP Corporate", Danielle!M:M, {"Manager Interview","Screening","Resume Intake/Review","Sourcing"}))
6 Replies
Found my solve = =SUM(COUNTIFS(Danielle!A:A, "IP Corporate", Danielle!M:M, {"Manager Interview","Screening","Resume Intake/Review","Sourcing"}))
I have =COUNTIFS(Danielle!A:A,"IP Corporate",Danielle!M:M,"Resume Intake/Review") + COUNTIFS(Danielle!A:A,"IP Corporate",Danielle!M:M,"Manager Interview") + COUNTIFS(Danielle!A:A,"IP Corporate",Danielle!M:M,"Resume Intake/Review") + COUNTIFS(Danielle!A:A,"IP Corporate",Danielle!M:M,"Screening"), but I was just hoping to not have so many COUNTIFS to avoid errors.
Thanks all for the input
- You could also try:
=SUMPRODUCT((Danielle!A:A="IP Corporate")*(Danielle!M:M={"Resume Intake/Review","Manager Interview","Screening"})) I think you have a repeat in that list. but I think using that chain of countifs may actually be quicker than using the sumproduct. That said, did the sumproduct not work?
=SUMRPODUCT( (Danielle!A:A="IP Corporate") * (
(Danielle!M:M="Resume Intake/Review") + (Danielle!M:M="Manager Interview") + (Danielle!M:M="Screening") )
- this formula isn't right (but obviously you realize that):
=countifs( Danielle!A:A, "Interplastic",
Danielle!M:M, "Resume Intake/Review",
"Screening","Manager Interview",
"Sourcing","Agency Search")
notice how I grouped pairs because COUNTIFS wants to check all the arrays in the first item of those pairs to see if corresponding items match the criteria in the second item of the pair. But the last couple pairs don't even have an array to do a lookup on.
a more flexible way is to use SUMPRODUCT()
=SUMPRODUCT( (Danielle!A:A="Interplastic")*(Danielle!M:M="Resume Intake/Review")+(Danielle!M:M="Screening")+(Danielle!M:M="Manager Interview")+(Danielle!M:M="Sourcing")+(Danielle!M:M="Agency Search") ) )
so in this case you get a TRUE or FALSE in each case and by using + it acts like an OR and then using a * it acts like an AND. =countifs(Danielle!A:A, "Interplastic", Danielle!M:M, "Resume Intake/Review", Danielle!O:O, "Screening", Danielle!P:P, "Manager Interview", Danielle!Z:Z, "Sourcing", Danielle!T:T, "Agency Search")Does it work if you add criteria ranges for the other criteria ( "Screening","Manager Interview","Sourcing","Agency Search" ) ?
EDIT:
=SUMPRODUCT((Danielle!A:A= "Interplastic")*(( Danielle!M:M= "Resume Intake/Review")+(Danielle!M:M="Screening")+(Danielle!M:M="Manager Interview")+(Danielle!M:M="Sourcing")+(Danielle!M:M="Agency Search")))Maybe you want Screening or Manager Interview or Sourcing or Agency Search in range Danielle!M:M.
