Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
I have 3 colums of data with multiple rows. the first two columns contain
"x"'s while the third contains 1 alphabet. I'm trying to set up a formula that says if column 3=N, and there is an "x" in column 1 or 2 count "x" seperatly in each column. (ie. total column would be N, E S,W, then each row would have a total for "x"'s in that particular row. Same thing, if Column 3=N, if Column 3=S, if Column 3=W. I've been trying for days to get this and I'm giving up. Can any of you nice folks help? -- Thank you for you help MO Albany, NY |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
Your statement of the problem isn't too clear but, assuming the Ns, Es, etc
are in column C and the Xs are in columns A & B, you can use this count the entries in col A =SUMPRODUCT(--(A1:A100="X"),--(C1:C100="N")) and this for col B =SUMPRODUCT(--(B1:B100="X"),--(C1:C100="N")) "MO" wrote: I have 3 colums of data with multiple rows. the first two columns contain "x"'s while the third contains 1 alphabet. I'm trying to set up a formula that says if column 3=N, and there is an "x" in column 1 or 2 count "x" seperatly in each column. (ie. total column would be N, E S,W, then each row would have a total for "x"'s in that particular row. Same thing, if Column 3=N, if Column 3=S, if Column 3=W. I've been trying for days to get this and I'm giving up. Can any of you nice folks help? -- Thank you for you help MO Albany, NY |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
Thank you for your help Duke. Your assumptions are correct; however, it does
not work. I need a count of how many x's are in column A for each(N E S W) and I need a count of how many x's are in column B for the same. I know I have to list N E S W somehwhere and then I need a count for x's for each to appear. -- Thank you for you help MO Albany, NY "Duke Carey" wrote: Your statement of the problem isn't too clear but, assuming the Ns, Es, etc are in column C and the Xs are in columns A & B, you can use this count the entries in col A =SUMPRODUCT(--(A1:A100="X"),--(C1:C100="N")) and this for col B =SUMPRODUCT(--(B1:B100="X"),--(C1:C100="N")) "MO" wrote: I have 3 colums of data with multiple rows. the first two columns contain "x"'s while the third contains 1 alphabet. I'm trying to set up a formula that says if column 3=N, and there is an "x" in column 1 or 2 count "x" seperatly in each column. (ie. total column would be N, E S,W, then each row would have a total for "x"'s in that particular row. Same thing, if Column 3=N, if Column 3=S, if Column 3=W. I've been trying for days to get this and I'm giving up. Can any of you nice folks help? -- Thank you for you help MO Albany, NY |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
On Fri, 10 Mar 2006 06:41:29 -0800, MO
wrote: Thank you for your help Duke. Your assumptions are correct; however, it does not work. I need a count of how many x's are in column A for each(N E S W) and I need a count of how many x's are in column B for the same. I know I have to list N E S W somehwhere and then I need a count for x's for each to appear. Could you post some typical data in the layout you have, and indicate what you expect to see as a result. That would enable us to better understand your requirement. Rgds Richard Buttrey __ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
What exactly do you mean by "it does not work"?
"MO" wrote: Thank you for your help Duke. Your assumptions are correct; however, it does not work. I need a count of how many x's are in column A for each(N E S W) and I need a count of how many x's are in column B for the same. I know I have to list N E S W somehwhere and then I need a count for x's for each to appear. -- Thank you for you help MO Albany, NY "Duke Carey" wrote: Your statement of the problem isn't too clear but, assuming the Ns, Es, etc are in column C and the Xs are in columns A & B, you can use this count the entries in col A =SUMPRODUCT(--(A1:A100="X"),--(C1:C100="N")) and this for col B =SUMPRODUCT(--(B1:B100="X"),--(C1:C100="N")) "MO" wrote: I have 3 colums of data with multiple rows. the first two columns contain "x"'s while the third contains 1 alphabet. I'm trying to set up a formula that says if column 3=N, and there is an "x" in column 1 or 2 count "x" seperatly in each column. (ie. total column would be N, E S,W, then each row would have a total for "x"'s in that particular row. Same thing, if Column 3=N, if Column 3=S, if Column 3=W. I've been trying for days to get this and I'm giving up. Can any of you nice folks help? -- Thank you for you help MO Albany, NY |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
Sorry for the confusion. Here's what I'm trying to do.
Col 1 Col 2 Col 3 x N x E x S x W x S x E x N x N x N x E N 0 0 E S W -- Thank you for you help MO Albany, NY "Richard Buttrey" wrote: On Fri, 10 Mar 2006 06:41:29 -0800, MO wrote: Thank you for your help Duke. Your assumptions are correct; however, it does not work. I need a count of how many x's are in column A for each(N E S W) and I need a count of how many x's are in column B for the same. I know I have to list N E S W somehwhere and then I need a count for x's for each to appear. Could you post some typical data in the layout you have, and indicate what you expect to see as a result. That would enable us to better understand your requirement. Rgds Richard Buttrey __ |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
On Fri, 10 Mar 2006 07:33:00 -0800, MO
wrote: Sorry for the confusion. Here's what I'm trying to do. Col 1 Col 2 Col 3 x N x E x S x W x S x E x N x N x N x E N 0 0 E S W Thanks, With the data above in A10:C10, enter N, E, S, W into E1:E4 Then put =SUMPRODUCT((A$1:A$10="x")*($C$1:$C$10=$E1)) into F1 and copy to F1:G4 HTH Richard Buttrey __ |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
Richard,
I was off on 3/13. It "kinda" works! However, there are two counts I need. I need the cound of all N, E, S, W by column and then a total of all x's in each column. Here's another sample of what I'm trying to do. I manually put totals in where I need them. Any other assistance you can provide is truly appreciated. Thank you so much for your patience. x N x E x S x W x S x E x N x N x N x E TOTALS N 2 2 E 1 2 S 2 0 W 1 0 TOTALS 6 2 -- MO Albany, NY "Richard Buttrey" wrote: On Fri, 10 Mar 2006 07:33:00 -0800, MO wrote: Sorry for the confusion. Here's what I'm trying to do. Col 1 Col 2 Col 3 x N x E x S x W x S x E x N x N x N x E N 0 0 E S W Thanks, With the data above in A10:C10, enter N, E, S, W into E1:E4 Then put =SUMPRODUCT((A$1:A$10="x")*($C$1:$C$10=$E1)) into F1 and copy to F1:G4 HTH Richard Buttrey __ |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
On Tue, 14 Mar 2006 05:58:34 -0800, MO
wrote: Richard, I was off on 3/13. It "kinda" works! However, there are two counts I need. I need the cound of all N, E, S, W by column and then a total of all x's in each column. Here's another sample of what I'm trying to do. I manually put totals in where I need them. Any other assistance you can provide is truly appreciated. Thank you so much for your patience. x N x E x S x W x S x E x N x N x N x E TOTALS N 2 2 E 1 2 S 2 0 W 1 0 TOTALS 6 2 Hi, The SUMPRODUCT formula works for me OK. Just to repeat. Put your example table above in A1:C10 (i.e. the x's in columns A & B and the N, E, S W etc in Col C. Then in E1:E4 enter N, E, S & W Then in F1 enter =SUMPRODUCT((A$1:A$10="x")*($C$1:$C$10=$E1)) This will result in '2' i,e the number of N's in col 1. Now copy this formula to F1:G4 and you should have the small summary table you identify above. The totals of all the x's is of course a simple SUM(F1:F4) formula in F5 and SUM(G1:G4) in G5 Was it a typo above when you say the total number of x's in col B is 2. Surely that should be 4? If I'm still misunderstanding your problem, please post back. Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
Richard,
Thanks for your patience. I manually typed in totals for Column B, that is why they are not accurate. Your formula works. However, it gives me the number of x's in Column A only not Column B. It seems confusing for me to have the totals in columns E & F. I would like to have the totals in the same column (i.e. totals for column A in Column A, etc.) For example there are 6 x's in column A. I would like below column A to show 2 x's are N, 1 x is E, 2x's are S, 1 x is W. Then in the same column I would like to total the number of x's, which in this case is 6. So if you add N, E, S, W in column A you get 6. Does this make sense? I'm sorry for the long delay, but I will be checking all day to see if you can help. I appreciate your assist thus far -- Thank you for you help MO Albany, NY "Richard Buttrey" wrote: On Tue, 14 Mar 2006 05:58:34 -0800, MO wrote: Richard, I was off on 3/13. It "kinda" works! However, there are two counts I need. I need the cound of all N, E, S, W by column and then a total of all x's in each column. Here's another sample of what I'm trying to do. I manually put totals in where I need them. Any other assistance you can provide is truly appreciated. Thank you so much for your patience. x N x E x S x W x S x E x N x N x N x E TOTALS N 2 2 E 1 2 S 2 0 W 1 0 TOTALS 6 2 Hi, The SUMPRODUCT formula works for me OK. Just to repeat. Put your example table above in A1:C10 (i.e. the x's in columns A & B and the N, E, S W etc in Col C. Then in E1:E4 enter N, E, S & W Then in F1 enter =SUMPRODUCT((A$1:A$10="x")*($C$1:$C$10=$E1)) This will result in '2' i,e the number of N's in col 1. Now copy this formula to F1:G4 and you should have the small summary table you identify above. The totals of all the x's is of course a simple SUM(F1:F4) formula in F5 and SUM(G1:G4) in G5 Was it a typo above when you say the total number of x's in col B is 2. Surely that should be 4? If I'm still misunderstanding your problem, please post back. Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count function not working, keeps displaying 0 | Excel Worksheet Functions | |||
Function to count unique values? | Excel Worksheet Functions | |||
clock | Excel Worksheet Functions | |||
Count If Function | Excel Worksheet Functions | |||
count if function with if statements | Excel Worksheet Functions |