Counting visible rows in a filter set that meet specific criterea in two columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1394135%22%20slang%3D%22en-US%22%3ECounting%20visible%20rows%20in%20a%20filter%20set%20that%20meet%20specific%20criterea%20in%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1394135%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20worksheet%20containing%20data%20that%20has%20sales%20pipeline%20information%20in%20it%20for%20the%20entire%20company%20whose%20data%20set%20includes%20rows%2010-3153.%26nbsp%3B%20I%20have%20applied%20a%20filter%20in%20one%20of%20the%20columns%20to%20narrow%20down%20the%20data%20to%20a%20single%20business%20unit%2C%20which%20gives%20me%20a%20result%20set%20to%20work%20from.%26nbsp%3B%20In%20that%20filter%20result%20set%20of%20visible%20rows%2C%20I%20need%20to%20count%20the%20rows%20in%20which%20particular%20text%20(US%20Comm%2FCSI%20Market)%20appears%20in%20column%20G%2C%20AND%20text%20(Won)%20also%20appears%20in%20Column%20K.%26nbsp%3B%20IF%20the%20specified%20text%20appears%20in%20columns%20G%20%26amp%3B%20K%2C%20then%20it%20counts%20that%20row.%26nbsp%3B%20I%20have%20been%20roaming%20around%20trying%20various%20permutations%20of%20both%20SUBTOTAL%20and%20SUMPRODUCT%20all%20day%20long%2C%20but%20can't%20seem%20to%20find%20any%20combination%20that%20works.%26nbsp%3B%20HELP%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1394135%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-1394176%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20visible%20rows%20in%20a%20filter%20set%20that%20meet%20specific%20criterea%20in%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1394176%22%20slang%3D%22en-US%22%3EYou%20can%20attach%20your%20file%20for%20better%20understanding%20of%20how%20your%20data%20is%20structured%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1394185%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20visible%20rows%20in%20a%20filter%20set%20that%20meet%20specific%20criterea%20in%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1394185%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3ESpecifically%2C%20you%20will%20see%20what%20I%20am%20trying%20to%20do%20at%20the%20bottom%20of%20the%20CQ%20Opp%20View%20Tab.%26nbsp%3B%20If%20I%20can%20get%20one%20formula%20to%20handle%20cell%20E3195%2C%20then%20I%20should%20be%20able%20to%20adapt%20it%20to%20the%20other%20criteria.%26nbsp%3B%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1395063%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20visible%20rows%20in%20a%20filter%20set%20that%20meet%20specific%20criterea%20in%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1395063%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F669590%22%20target%3D%22_blank%22%3E%40wapiti59%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20add%20helper%20column%20let%20say%20Y)%20with%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DAGGREGATE(3%2C5%2CA9)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Edrag%20it%20down%20till%20the%20end%20of%20the%20range.%20Formula%20returns%201%20for%20visible%20rows%20and%20zero%20for%20hided%20ones.%3C%2FP%3E%0A%3CP%3EWith%20that%20counting%20could%20be%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIFS(%24G%2410%3A%24G%242538%2C%24A2542%2C%24K%2410%3A%24K%242538%2C%22Won%22%2C%24Y%2410%3A%24Y%242538%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1394396%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20visible%20rows%20in%20a%20filter%20set%20that%20meet%20specific%20criterea%20in%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1394396%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F669590%22%20target%3D%22_blank%22%3E%40wapiti59%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20try%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIFS(Table1%5BMarket%5D%2CG4%2CTable1%5BRM%20Status%5D%2CK4)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20the%20attached%20workbook.%20Note%20that%20I%20formatted%20the%20data%20as%20a%20table%2C%20hence%20the%20structured%20referencing.%20%5BTable%20is%20very%20useful%20as%20the%20formula%20automatically%20adjusts%20to%20include%20any%20new%20data%20subsequently%2C%20hence%20you%20may%20consider%20this%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20typed%20in%20the%20criteria%20on%20both%20cells%20G4%20and%20K4%20to%20make%20it%20easier%20to%20change.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20if%20you%20prefer%20not%20to%20use%20a%20table%2C%20the%20formula%20will%20be%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIFS('CQ%20Opp%20View'!%24G%2410%3A%24G%243191%2CG4%2C'CQ%20Opp%20View'!%24K%2410%3A%24K%243191%2CK4)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20let%20me%20know%20if%20you%20need%20additional%20clarification.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have a worksheet containing data that has sales pipeline information in it for the entire company whose data set includes rows 10-3153.  I have applied a filter in one of the columns to narrow down the data to a single business unit, which gives me a result set to work from.  In that filter result set of visible rows, I need to count the rows in which particular text (US Comm/CSI Market) appears in column G, AND text (Won) also appears in Column K.  IF the specified text appears in columns G & K, then it counts that row.  I have been roaming around trying various permutations of both SUBTOTAL and SUMPRODUCT all day long, but can't seem to find any combination that works.  HELP?

4 Replies
Highlighted
You can attach your file for better understanding of how your data is structured
Highlighted

@Abiola1Specifically, you will see what I am trying to do at the bottom of the CQ Opp View Tab.  If I can get one formula to handle cell E3195, then I should be able to adapt it to the other criteria.  Thank you!

Highlighted

Hi @wapiti59 

 

You can try this

 

 

=COUNTIFS(Table1[Market],G4,Table1[RM Status],K4)

 

 

See the attached workbook. Note that I formatted the data as a table, hence the structured referencing. [Table is very useful as the formula automatically adjusts to include any new data subsequently, hence you may consider this]

 

I also typed in the criteria on both cells G4 and K4 to make it easier to change.

 

However, if you prefer not to use a table, the formula will be:

 

=COUNTIFS('CQ Opp View'!$G$10:$G$3191,G4,'CQ Opp View'!$K$10:$K$3191,K4)

 

Do let me know if you need additional clarification.

 

Cheers,

 

Highlighted

@wapiti59 

You may add helper column let say Y) with formula

=AGGREGATE(3,5,A9)

drag it down till the end of the range. Formula returns 1 for visible rows and zero for hided ones.

With that counting could be as

=COUNTIFS($G$10:$G$2538,$A2542,$K$10:$K$2538,"Won",$Y$10:$Y$2538,1)