Home

COUNTIFS using criteria from both column A and B

%3CLINGO-SUB%20id%3D%22lingo-sub-38355%22%20slang%3D%22en-US%22%3ECOUNTIFS%20using%20criteria%20from%20both%20column%20A%20and%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-38355%22%20slang%3D%22en-US%22%3E%3CP%3EI%20can't%20figure%20out%20how%20to%20combine%20the%20formulas%20%3DCOUNTIF(B%3AB%2C%22IHC*%22)%2BCOUNTIF(B%3AB%2C%22SS*%22)%20and%20%3DCOUNTIFS(A%3AA%2C%22%26gt%3B1330%22%2CA%3AA%2C%22%26lt%3B1431%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20only%20count%20the%20criteria%20in%20column%20A%20if%20the%20criteria%20in%20column%20B%20is%20also%20met.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-38355%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-38560%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20using%20criteria%20from%20both%20column%20A%20and%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-38560%22%20slang%3D%22en-US%22%3E%3CP%3EAnna%2C%20that%20is%20the%20imitation%20of%20OR%20condition.%20In%20brief%2C%20COUNTIFS%20on%20array%20of%20conditions%20also%20generates%20an%20array%2C%20after%20that%20you%20just%20take%20the%20sum%20of%20it.%20Like%20SUM(%7B5%2C7%7D)%3D12%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-38549%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20using%20criteria%20from%20both%20column%20A%20and%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-38549%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you!%20I%20don't%20understand%20this%20one%2C%20but%20it%20gave%20me%20the%20same%20result%20as%20above!%20%3Asmileyhappy%3A%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-38548%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20using%20criteria%20from%20both%20column%20A%20and%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-38548%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%2C%20it%20worked!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-38427%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20using%20criteria%20from%20both%20column%20A%20and%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-38427%22%20slang%3D%22en-US%22%3E%3CP%3EOr%20bit%20more%20compact%26nbsp%3B%3DSUM(COUNTIFS(B%3AB%2C%7B%22SS*%22%3B%22IHC*%22%7D%2CA%3AA%2C%22%26gt%3B1330%22%2CA%3AA%2C%22%26lt%3B1431%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-38383%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20using%20criteria%20from%20both%20column%20A%20and%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-38383%22%20slang%3D%22en-US%22%3EHi%20Anna%2C%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20probably%20need%20to%20do%202%20COUNTIFS%20given%20you%20want%20to%20count%20IHC*%20or%20SS*%3CBR%20%2F%3E%3CBR%20%2F%3E%3DCOUNTIFS(A%3AA%2C%22%26gt%3B1330%22%2CA%3AA%2C%22%26lt%3B1431%22%2CB%3AB%2C%22IHC*%22)%20%2B%20COUNTIFS(A%3AA%2C%22%26gt%3B1330%22%2CA%3AA%2C%22%26lt%3B1431%22%2CB%3AB%2C%22SS*%22)%3C%2FLINGO-BODY%3E
Anna McHaffie
New Contributor

I can't figure out how to combine the formulas =COUNTIF(B:B,"IHC*")+COUNTIF(B:B,"SS*") and =COUNTIFS(A:A,">1330",A:A,"<1431")

 

I want to only count the criteria in column A if the criteria in column B is also met.

 

Thank you,

5 Replies
Hi Anna,

You probably need to do 2 COUNTIFS given you want to count IHC* or SS*

=COUNTIFS(A:A,">1330",A:A,"<1431",B:B,"IHC*") + COUNTIFS(A:A,">1330",A:A,"<1431",B:B,"SS*")
Highlighted

Or bit more compact =SUM(COUNTIFS(B:B,{"SS*";"IHC*"},A:A,">1330",A:A,"<1431"))

Thank you, it worked!

Thank you! I don't understand this one, but it gave me the same result as above! :smileyhappy:

Anna, that is the imitation of OR condition. In brief, COUNTIFS on array of conditions also generates an array, after that you just take the sum of it. Like SUM({5,7})=12