Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex or Conditional Countif(s)
I'm needing to count stuff in a column based on a condition in another
column. Here's my example: Reasons Plant A B C D E 102 X 999 X 102 X 102 X I want to set up a matrix where I count each reason by Plant.........so..... For Reason #A - My count for 102 would be 2 and for 999 would be 1 For Reason #C - My count for 102 would be 1 and for 999 would be 0 Does anyone know of a formula to use; I'm stumped. Thanks. Booweezie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex or Conditional Countif(s)
Boo -- Here's one approach. Tried it -- seems to work
A B C D E 1 Reasons 2 A B C 3 102 X 4 999 X 5 102 X 6 102 X 7 8 102 Formula here 9 999 In cell C8, put the following formula: =SUMPRODUCT(--($C$3:$C$6="X"),(--($A$3:$A$6=A8))) You can copy it down to C9. There's lots more powerful tools -- Filtering, PivotTables, etc. But this is an easy way. "Booweezie" wrote: I'm needing to count stuff in a column based on a condition in another column. Here's my example: Reasons Plant A B C D E 102 X 999 X 102 X 102 X I want to set up a matrix where I count each reason by Plant.........so..... For Reason #A - My count for 102 would be 2 and for 999 would be 1 For Reason #C - My count for 102 would be 1 and for 999 would be 0 Does anyone know of a formula to use; I'm stumped. Thanks. Booweezie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex or Conditional Countif(s)
=SUMPRODUCT(--(A2:A50=102),--(B2:B50<""))
where A2:A50 is the plant range and B2:B50 reason A range if you are using X you can change it to =SUMPRODUCT(--(A2:A50=102),--(B2:B50="X")) replace 102 with =SUMPRODUCT(--(A2:A50=H1),--(B2:B50<"")) where you put the plant number in H1 -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Booweezie" wrote in message ... I'm needing to count stuff in a column based on a condition in another column. Here's my example: Reasons Plant A B C D E 102 X 999 X 102 X 102 X I want to set up a matrix where I count each reason by Plant.........so..... For Reason #A - My count for 102 would be 2 and for 999 would be 1 For Reason #C - My count for 102 would be 1 and for 999 would be 0 Does anyone know of a formula to use; I'm stumped. Thanks. Booweezie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex or Conditional Countif(s)
pdberger - You're a peach!
That's exactly what I needed and it works perfectly! Check's in the mail! ~~Booweezie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format Not Working | Excel Discussion (Misc queries) | |||
complex conditional format | Excel Discussion (Misc queries) | |||
complex conditional formulas in excel using "IF" | Excel Worksheet Functions | |||
complex?? Q about Conditional formatting | Excel Worksheet Functions | |||
Complex Conditional formulas | Excel Worksheet Functions |