May 15 2020 02:13 PM
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?
May 15 2020 02:23 PM
May 15 2020 02:29 PM
@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!
May 15 2020 04:09 PM - edited May 16 2020 03:05 AM
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,
May 16 2020 02:34 AM
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)