Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Sum If and an array?
Hi all,
I have the data below and want to do the following: In ONE cell, I want there to be a count of the IDs that have an X in more than one rule. In the case below, ID 1 would be counted, but ID 2 would not, ID 3 would, etc. Any help? Thanks! ID Rule 1 Rule 2 Rule 3 Rule 4 Rule 5 1 X X 2 X 3 X X 4 5 X 6 X 7 X X 8 X 9 X 10 X 11 12 13 X X 14 15 X 16 X 17 18 X 19 X 20 X 21 X 22 X X 23 X X X |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Sum If and an array?
By the way, if it makes it easier, the Xs are actually 1s. I don't know if
this makes using some sort of sum function easier. "Jonathan" wrote: Hi all, I have the data below and want to do the following: In ONE cell, I want there to be a count of the IDs that have an X in more than one rule. In the case below, ID 1 would be counted, but ID 2 would not, ID 3 would, etc. Any help? Thanks! ID Rule 1 Rule 2 Rule 3 Rule 4 Rule 5 1 X X 2 X 3 X X 4 5 X 6 X 7 X X 8 X 9 X 10 X 11 12 13 X X 14 15 X 16 X 17 18 X 19 X 20 X 21 X 22 X X 23 X X X |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Sum If and an array?
If you only need a count, you could add a helper formula in a column
after your data that counts the 1s like =IF(COUNT(B2:F2)1,1,0) and then where ever you want to display the actual count you could just sum that range. On May 6, 3:30 pm, Jonathan wrote: By the way, if it makes it easier, the Xs are actually 1s. I don't know if this makes using some sort of sum function easier. "Jonathan" wrote: Hi all, I have the data below and want to do the following: In ONE cell, I want there to be a count of the IDs that have an X in more than one rule. In the case below, ID 1 would be counted, but ID 2 would not, ID 3 would, etc. Any help? Thanks! ID Rule 1 Rule 2 Rule 3 Rule 4 Rule 5 1 X X 2 X 3 X X 4 5 X 6 X 7 X X 8 X 9 X 10 X 11 12 13 X X 14 15 X 16 X 17 18 X 19 X 20 X 21 X 22 X X 23 X X X |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Sum If and an array?
Hi there,
You could use something like this... =SUMPRODUCT(--(((B2:B24=1)+(C2:C24=1)+(D2:D24=1)+(E2:E24=1)+(F2: F24=1))1)) It would not, however, identify which items (IDs) would quantify that return. To do that you would probably be best off with a helper column. You could also do this in a pivot table. -- Zack Barresse "Jonathan" wrote in message ... Hi all, I have the data below and want to do the following: In ONE cell, I want there to be a count of the IDs that have an X in more than one rule. In the case below, ID 1 would be counted, but ID 2 would not, ID 3 would, etc. Any help? Thanks! ID Rule 1 Rule 2 Rule 3 Rule 4 Rule 5 1 X X 2 X 3 X X 4 5 X 6 X 7 X X 8 X 9 X 10 X 11 12 13 X X 14 15 X 16 X 17 18 X 19 X 20 X 21 X 22 X X 23 X X X |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Sum If and an array?
Even though the requirements of the OP was for "one cell" formula, I agree
about the helper column. It opens the door to many other possibilities in regards to identification and such. -- Zack Barresse "Reitanos" wrote in message ... If you only need a count, you could add a helper formula in a column after your data that counts the 1s like =IF(COUNT(B2:F2)1,1,0) and then where ever you want to display the actual count you could just sum that range. On May 6, 3:30 pm, Jonathan wrote: By the way, if it makes it easier, the Xs are actually 1s. I don't know if this makes using some sort of sum function easier. "Jonathan" wrote: Hi all, I have the data below and want to do the following: In ONE cell, I want there to be a count of the IDs that have an X in more than one rule. In the case below, ID 1 would be counted, but ID 2 would not, ID 3 would, etc. Any help? Thanks! ID Rule 1 Rule 2 Rule 3 Rule 4 Rule 5 1 X X 2 X 3 X X 4 5 X 6 X 7 X X 8 X 9 X 10 X 11 12 13 X X 14 15 X 16 X 17 18 X 19 X 20 X 21 X 22 X X 23 X X X |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Sum If and an array?
Nope, only count needed and this did the trick--thanks!
"Zack Barresse" wrote: Hi there, You could use something like this... =SUMPRODUCT(--(((B2:B24=1)+(C2:C24=1)+(D2:D24=1)+(E2:E24=1)+(F2: F24=1))1)) It would not, however, identify which items (IDs) would quantify that return. To do that you would probably be best off with a helper column. You could also do this in a pivot table. -- Zack Barresse "Jonathan" wrote in message ... Hi all, I have the data below and want to do the following: In ONE cell, I want there to be a count of the IDs that have an X in more than one rule. In the case below, ID 1 would be counted, but ID 2 would not, ID 3 would, etc. Any help? Thanks! ID Rule 1 Rule 2 Rule 3 Rule 4 Rule 5 1 X X 2 X 3 X X 4 5 X 6 X 7 X X 8 X 9 X 10 X 11 12 13 X X 14 15 X 16 X 17 18 X 19 X 20 X 21 X 22 X X 23 X X X |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate an array that is a subset of a larger array? | Excel Worksheet Functions | |||
Array: Counting multiple values within array | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
Goal Seek On Members of an Array within Array | Excel Worksheet Functions |