Forum Discussion
Counting visible rows in a filter set that meet specific criterea in two columns
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
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)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,
- You can attach your file for better understanding of how your data is structured
