Help with counting two different criterias.

[Jboo]

Hello, I need help with a counting criteria I have in two different columns.
For example I need to know how many "40"'s from column A are Comp or OUTST or
Above and so on from Column B, then how many "83"s are COMP or OUTST or ABOVE
and so on. I have used the CountIf function but it will only do the first
criteria.
A B
43 COMP
83 COMP
83 COMP
40 COMP
43 COMP
40 COMP
40 OUTST
40 ABOVE
40 NEDVL
83 UNSAT

 

[Luke M]

You can use the SUMPRODUCT
=SUMPRODUCT((A2:A100=40)*(B2:B100="COMP"))

Adjust ranges/values as needed.

 

[Rick Rothstein]

Try this formula...

=SUMPRODUCT((A1:A1000=40)*((B1:B1000="COMP")+(B1:B1000="OUTST")+(B1:B1000="ABOVE")))

Each specified range must contain the same number of elements. I assumed your data started at A1, but you can change all the row references from 1 to whichever row your data starts in. I chose 1000 as the last possible row where your data could be... this number (in all the ranges) needs to be a row number that is larger than the maximum row your ever expect to put data in (it is alright if Columns A and B have no data in their cells for the range specified... the formula will ignore them... so it is alright to use a row number larger than the actual data you now have).

 

[xlmate]

Do you want to count the number of 40 from column A that are Comp in column B
and the number of 40 in column A that are OUST in column B, each of these
retrun
in different cells?

if yes, try this in C2. Assume that your data start from row 2

=SUMPRODUCT(--(A2:A11=40),--(B2:B11="COMP"))

if you want to count 40 in col A that are Comp,Oust,Above
use this
=SUMPRODUCT(--(A2:A11=40),--(B2:B11="COMP"))+SUMPRODUCT(--(A2:A11=40),--(B2:B11="OUTST"))+SUMPRODUCT(--(A2:A11=40),--(B2:B11="ABOVE"))

--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.


Thank You

cheers, francis

 

[Rick Rothstein]

I think I may have misread your question. Did you want the count for COMP **or** "OUTST" **or** "ABOVE (that is what I gave you)? Or did you want it for each of those separately. If separately...

=SUMPRODUCT((A1:A1000=40)*(B1:B1000="COMP"))
=SUMPRODUCT((A1:A1000=40)*(B1:B1000="OUTST"))
=SUMPRODUCT((A1:A1000=40)*(B1:B1000="ABOVE"))

Change what the first logical expression is equal to (40 in the above example) to whatever other values you want. You may want to use a cell reference rather than a hard-coded number to make changing values easier... just replace the 40 with, say, C1 and put your number (40 for the above example) into C1.

--
Rick (MVP - Excel)


Try this formula...

=SUMPRODUCT((A1:A1000=40)*((B1:B1000="COMP")+(B1:B1000="OUTST")+(B1:B1000="ABOVE")))

Each specified range must contain the same number of elements. I assumed your data started at A1, but you can change all the row references from 1 to whichever row your data starts in. I chose 1000 as the last possible row where your data could be... this number (in all the ranges) needs to be a row number that is larger than the maximum row your ever expect to put data in (it is alright if Columns A and B have no data in their cells for the range specified... the formula will ignore them... so it is alright to use a row number larger than the actual data you now have).

 

[Bob Phillips]

=SUMPRODUCT((A1:A1000=40)*(B1:B1000={"COMP","OUTST","ABOVE"}))

 

[Jboo]

Thanks, this did not work but it got me started in the right direction. I used

{=sum((A2:A100=40)*(B2:B100="COMP"))}

Thanks again though!!

 

[Jboo]

thanks a bunch, this worked except for my purposes I needed the "COMP",
"OUTST" counted out seperatly. So i just removed the extra words within the
bracket.

 

[Rick Rothstein]

Well, it should have worked. Your array-entered SUM formula and the normally entered SUMPRODUCT formula that others have posted are equivalent... if one works, the other should also work. You might want to try it again (copy/paste the formula rather than retype it).

 

[Bob Phillips]

Then you wouldn't need the curly brackets either.