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

[wapiti59]

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?

[#2]

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

[#3]

@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!

[#4]

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,

[#5]

@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)