Possible to get totals based on various criteria?

Hi Ron,

You wrote of "thousands of possible combinations." I want to make sure I understand that for any single particular record, all 26 'switches' could be either on or off. Or is it that the logic need only check for certain combination of switches, which would greatly reduce the logic checks.

Kevin

Re: Possible to get totals ...

Ron, suppose your data looked like this...

{"ID","Crit1","Crit2","Crit3"
;"100","X","X",""
;"101","","X",""
;"102","","X","X"
;"103","X","X",""
;"104","X","","X"
;"105","","X","X"}

You could add a column labeled "Combo" and enter
the array formula...

{=SUM((B2:D2="X")*(2^TRANSPOSE(ROW($1:$3)-1)))}

...directly beneath the label on the 1st data row,
and then Copy down to the bottom of the data set.

If you're not familiar with array formulas please
note that they must be entered using the
Control+Shift+Enter key combination, and that the
braces,{}, are not entered by you.

This formula assigns a value (power of 2) to each
unique set of X's. In your case you'd need to
change the arguments of the formula's ROW()
function from $1:$3 to $1:$26. Once you created
the new 'Combo' values then all you'd need to do
is create a PivotTable to Count ID's by 'Combo'
value.

Let me know if you have additional questions.

Thanks for the quick response Kevin!
Each record could have any of the 26 switches on or off. We need to know how many records had switches on or off in any combination that exists.
For example, if the switches are letters of the alphabet A-Z we need to know how many had A only, how many had A,D,E how many had H,M,N,Z etc.
Hope that makes sense.

If that's what you want...

Then change the formula in my previous posting to...

{=VLOOKUP(SUM((B13:D13="X")*(2^TRANSPOSE(ROW($1:$3)-1))),{1,"A";2,"B";3,"AB";4,"C";5,"AC";6,"BC";7,"ABC"},2,0)}

Of course, the 2nd argument to the VLOOKUP() function
(that ugly array constant) could be replaced with a
cell reference containing the table. And, obviously,
a similar table would have to be setup for 26 variables,
but that would be a breeze. Let me know if you'd like
to pursue this option.

Back to the drawing board...

I calculated the number of rows required for that
lookup table -- it's over 67 million!! Needless
to say a table of this size would be impossible
to maintain in Excel. So now I'm back to my
original suggestion...

{=SUM(B2:AA2*(2^TRANSPOSE(ROW($1:$26)-1)))}

...At least this would allow you to investigate
your data, and identify clusters, outliers, etc.

Well, there's always brute force... : )

Make that...

Re: Make that...

Mark...Thanks for your suggestions. I'm going to give them a try later today. Yea, that would be one huge table.

Re: Make that...

: =IF(B2="X",B$1,"")&IF(C2="X",C$1,"")&IF(D2="X",D$1,"") : &IF(E2="X",E$1,"")&IF(F2="X",F$1,"")&IF(G2="X",G$1,"") : &IF(H2="X",H$1,"")&IF(I2="X",I$1,"")&IF(J2="X",J$1,"") : &IF(K2="X",K$1,"")&IF(L2="X",L$1,"")&IF(M2="X",M$1,"") : &IF(N2="X",N$1,"")&IF(O2="X",O$1,"")&IF(P2="X",P$1,"") : &IF(Q2="X",Q$1,"")&IF(R2="X",R$1,"")&IF(S2="X",S$1,"") : &IF(T2="X",T$1,"")&IF(U2="X",U$1,"")&IF(V2="X",V$1,"") : &IF(W2="X",W$1,"")&IF(X2="X",X$1,"")&IF(Y2="X",Y$1,"") : &IF(Z2="X",Z$1,"")&IF(AA2="X",AA$1,"")
:::::::::::::::::::::::::::::::::::::::;
:::::::::::::::::::::::::::::::::::::::;
Big Bob's suggestion is to have a 1 in a column if the switch is on;have a 0 in the column if the switch is off .Then for each record change all the switch columns to text and concatenate them to get a string of 26 characters .Sort on switch string to get all identical ones together then you need to see later postings regarding listing unique names and number of times each name occurs
Hope this is of some use
Big Bob