From given table i want to count number of text "Yes" from Result column where numeric value is 25 or 35 from number column. I want to use subtotal here as i have to filer data on groups.
Asked
Active
Viewed 58 times
-2
-
1Do you mean you want to use the `SUBTOTAL` function? – David Leal Jul 18 '23 at 04:08
-
A PivotTable will do it. – Black cat Jul 18 '23 at 05:09
-
@david leal - Yes – Rajiv Jul 18 '23 at 05:57
-
@Black cat - I want to use subtotal as i have to filter data. – Rajiv Jul 18 '23 at 06:17
1 Answers
0
I see the issue. Without making a too complicated formula try this: i. Add a new column (column D) with the following formula: "=+IF(AND(B2="Yes",OR(C2=25,C3=30)),"True","False")" -> This will create a column that shows the value TRUE if you have a "Yes" in Result column and 25 or 30 in Number column. ii. To get the SUBTOTAL of your search, add the following formula in the cell that needs to show the counted number: =SUMPRODUCT(SUBTOTAL(3,OFFSET(D2:D13,ROW(D2:D13)-ROW(D2),0,1)),--(D2:D13="TRUE")).
These two steps together will solve your issue
Dagobert
- 61
- 6