Counting Rows based on certain variable combinations

%3CLINGO-SUB%20id%3D%22lingo-sub-1727385%22%20slang%3D%22en-US%22%3ECounting%20Rows%20based%20on%20certain%20variable%20combinations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1727385%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHopefully%20my%20question%20is%20clear%2C%20and%20I%20hope%20the%20attachment%20shows%20up.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20a%20large%20spreadsheet%20with%20about%20a%20dozen%20columns%20of%20information.%20I%20would%20like%20to%20count%20how%20many%20rows%20have%20certain%20cells%20that%20have%20specific%20values%20in%20them.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EMy%20querying%20is%20much%20more%20extensive%20than%20what%20COUNTIFS%20can%20return%20and%20the%20various%20SUMPRODUCT%20arguments%20I've%20tried%20aren't%20cutting%20it%20either.%3CBR%20%2F%3E%3CBR%20%2F%3EGiven%208%20columns%20of%20data%20(after%20the%20column%20uniquely%20identifying%20each%20item%20being%20characterized%20by%20the%20data)%2C%20and%20each%20cell%20being%20possibly%20only%201%20of%202%20values%2C%20there%20are%20256%20different%20unique%20combinations%20of%20values%20in%20a%20given%20spreadsheet.%20Long%20story%20short%2C%20there%20are%20also%206560%20unique%20ways%20of%20quantifying%20the%20items%20based%20on%20the%20characteristics%20-%20ranging%20from%20returning%20quantities%20based%20on%20all%208%20cells%20associated%20with%20each%20data%20point%20or%20on%20only%20a%20few%20cells%20or%20even%20just%20one%20cell.%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20spreadsheet%20has%20thousands%20of%20rows%20of%20these%20combinations.%20Now%2C%20barring%20creating%20a%20COUNTIFS%20for%20each%20cell%20combination%2C%20which%20would%20be%20time-prohibitive%20to%20say%20the%20least%2C%20I%20was%20wondering%20if%20there%20was%20a%20formula%20short%20cut%20for%20this.%3CBR%20%2F%3E%3CBR%20%2F%3ELet%20me%20attach%20a%20slightly%20more%20intense%20example%20spreadsheet%20(hopefully%20without%20violating%20the%20forum%20guidelines).%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20hypothetical2%2C%20on%20the%20right%20side%20where%20I'm%20building%20my%20analysis%20spreadsheet%2C%20in%20some%20instances%20I'm%20concerned%20with%20combinations%20based%20on%20only%20a%20handful%20of%20cell's%20data%2C%20and%20in%20some%20instances%20I'm%20concerned%20with%20all%20the%20cells%2C%20and%20in%20some%20only%20a%20few%20cells.%20Now%2C%20as%20alluded%20to%20before%2C%20with%208%20columns%2C%20each%20column%20showing%201%20of%202%20variables...there%20could%20be%20up%20to%206560%20different%20ways%20to%20count%20the%20items%20if%20the%20items%20are%20counted%20considering%20all%20the%20cell's%20or%20only%20a%20handful%20of%20the%20cells%20associated%20with%20each%20item.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20hope%20I%20am%20not%20muddying%20the%20issue%2C%20and%20if%20Excel%20doesn't%20have%20a%20formula%20or%20a%20combination%20of%20formulas%20to%20handle%20that%20task%2C%20I'll%20just%20live%20with%20it.%20I've%20just%20wracked%20my%20excel-novice%20brain%20for%20what%20combination%20of%20nested%20formulas%20might%20solve%20my%20problem.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1727385%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1727407%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20Rows%20based%20on%20certain%20variable%20combinations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1727407%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F814272%22%20target%3D%22_blank%22%3E%40texasaggie0004%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20this%20is%20the%20solution%2C%20using%20wildcards%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIFS(B%3AB%2CIF(L3%26gt%3B0%2CL3%2C%22*%22)%2CC%3AC%2CIF(M3%26gt%3B0%2CM3%2C%22*%22)%2CD%3AD%2CIF(N3%26gt%3B0%2CN3%2C%22*%22)%2CE%3AE%2CIF(O3%26gt%3B0%2CO3%2C%22*%22)%2CF%3AF%2CIF(P3%26gt%3B0%2CP3%2C%22*%22)%2CG%3AG%2CIF(Q3%26gt%3B0%2CQ3%2C%22*%22)%2CH%3AH%2CIF(R3%26gt%3B0%2CR3%2C%22*%22)%2CI%3AI%2CIF(S3%26gt%3B0%2CS3%2C%22*%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1737146%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20Rows%20based%20on%20certain%20variable%20combinations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1737146%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F814272%22%20target%3D%22_blank%22%3E%40texasaggie0004%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223540iD8BF4DAEBE3515B9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ehere%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(--(MMULT(--((%24B%243%3A%24I%24100%3D%24L3%3A%24S3)%2B(%24L3%3A%24S3%3D%22%22))%2C%7B1%3B1%3B1%3B1%3B1%3B1%3B1%3B1%7D)%3D8))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Hopefully my question is clear, and I hope the attachment shows up.

I have a large spreadsheet with about a dozen columns of information. I would like to count how many rows have certain cells that have specific values in them.

 

My querying is much more extensive than what COUNTIFS can return and the various SUMPRODUCT arguments I've tried aren't cutting it either.

Given 8 columns of data (after the column uniquely identifying each item being characterized by the data), and each cell being possibly only 1 of 2 values, there are 256 different unique combinations of values in a given spreadsheet. Long story short, there are also 6560 unique ways of quantifying the items based on the characteristics - ranging from returning quantities based on all 8 cells associated with each data point or on only a few cells or even just one cell.

My spreadsheet has thousands of rows of these combinations. Now, barring creating a COUNTIFS for each cell combination, which would be time-prohibitive to say the least, I was wondering if there was a formula short cut for this.

Let me attach a slightly more intense example spreadsheet (hopefully without violating the forum guidelines).

In hypothetical2, on the right side where I'm building my analysis spreadsheet, in some instances I'm concerned with combinations based on only a handful of cell's data, and in some instances I'm concerned with all the cells, and in some only a few cells. Now, as alluded to before, with 8 columns, each column showing 1 of 2 variables...there could be up to 6560 different ways to count the items if the items are counted considering all the cell's or only a handful of the cells associated with each item.

I hope I am not muddying the issue, and if Excel doesn't have a formula or a combination of formulas to handle that task, I'll just live with it. I've just wracked my excel-novice brain for what combination of nested formulas might solve my problem.

Thanks!

2 Replies

@texasaggie0004 

 

I think this is the solution, using wildcards:

 

=COUNTIFS(B:B,IF(L3>0,L3,"*"),C:C,IF(M3>0,M3,"*"),D:D,IF(N3>0,N3,"*"),E:E,IF(O3>0,O3,"*"),F:F,IF(P3>0,P3,"*"),G:G,IF(Q3>0,Q3,"*"),H:H,IF(R3>0,R3,"*"),I:I,IF(S3>0,S3,"*"))

@texasaggie0004 

As variant

image.png

here

=SUMPRODUCT(--(MMULT(--(($B$3:$I$100=$L3:$S3)+($L3:$S3="")),{1;1;1;1;1;1;1;1})=8))