| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with counting two different criterias.
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with counting two different criterias.
You can use the SUMPRODUCT
=SUMPRODUCT((A2:A100=40)*(B2:B100="COMP")) Adjust ranges/values as needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jboo" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with counting two different criterias.
Try this formula...
=SUMPRODUCT((A1:A1000=40)*((B1:B1000="COMP")+(B1:B 1000="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). -- Rick (MVP - Excel) "Jboo" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with counting two different criterias.
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 "Jboo" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with counting two different criterias.
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) "Rick Rothstein" wrote in message ... Try this formula... =SUMPRODUCT((A1:A1000=40)*((B1:B1000="COMP")+(B1:B 1000="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). -- Rick (MVP - Excel) "Jboo" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with counting two different criterias.
=SUMPRODUCT((A1:A1000=40)*(B1:B1000={"COMP","OUTST ","ABOVE"}))
-- __________________________________ HTH Bob "Jboo" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with counting two different criterias.
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!! "Luke M" wrote: You can use the SUMPRODUCT =SUMPRODUCT((A2:A100=40)*(B2:B100="COMP")) Adjust ranges/values as needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jboo" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with counting two different criterias.
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. "Bob Phillips" wrote: =SUMPRODUCT((A1:A1000=40)*(B1:B1000={"COMP","OUTST ","ABOVE"})) -- __________________________________ HTH Bob "Jboo" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with counting two different criterias.
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).
-- Rick (MVP - Excel) "Jboo" wrote in message ... 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!! "Luke M" wrote: You can use the SUMPRODUCT =SUMPRODUCT((A2:A100=40)*(B2:B100="COMP")) Adjust ranges/values as needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jboo" wrote: 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with counting two different criterias.
Then you wouldn't need the curly brackets either.
-- __________________________________ HTH Bob "Jboo" wrote in message ... 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. "Bob Phillips" wrote: =SUMPRODUCT((A1:A1000=40)*(B1:B1000={"COMP","OUTST ","ABOVE"})) -- __________________________________ HTH Bob "Jboo" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Too many criterias... | Excel Worksheet Functions | |||
| If Criterias | Excel Discussion (Misc queries) | |||
| Sum If using 2 criterias | Excel Discussion (Misc queries) | |||
| Counting instances based on two criterias | Excel Worksheet Functions | |||
| Counting cells if special criterias are true | Excel Worksheet Functions | |||
Linear Mode
