Subtotal on a countif - calculating a % of a condition

[Johnnyboy1x]

Hello All --

I have been struggling with this. I want to be able to filter and return a result for a percent of cells that meet a condition. In my case -30 ---> +30 are green results and I am calculating the percentage of green from the whole total.

I tried to accomplish this using multiple subtotal formulas, from which the results would then calculate the percentage.

It works in theory, and when not filtering, but not when filtering. I can't get the countif statements right for the first formula.


DATA SET EXAMPLE --

status	serial	type	days from target
Closed	136761/13	PM	-35
Closed	128083/09	PM	-31
Closed	141305/15	PM	-3
Closed	123013/07	PM	-1
Closed	137902/14	INST	-1
Closed	133326/11	INST	32
Closed	125949/08	INST	33
Closed	141734/15	PM	34

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

FIRST FORMULA -- (where I am having the problem)

'=SUM(COUNTIF(D:D,">30")+(COUNTIF(D:D,"<-30"))) --- This works as it should and returns a result of 5 for the above data set. But, when I filter out the result doesn't change (as it shouldn't.

So I tried to do this (next formula below), but no matter what i do I can't get excel to like it...ask for missing parenthesis or arguments and no matter what I do its not happy. '=SUBTOTAL(109,(COUNTIF(D:D,">30")+(COUNTIF(D:D,"<-30")))

SECOND FORMULA --

'=SUBTOTAL(2,D:D). This works as it should and returns a count of 8 for the above data set and changes when filtering. GREAT!

THIRD FORMULA --

Cell formated as a percent and I calculate the percentage of the above 2 results with the formula below. Returns 38% as it should.

'=(F2-E2)/F2


HOW CAN I ACHIEVE THE SUBTOTALS FOR THE COUNT IF IN THE FIRST FORMULA OR DO I NEED A COMPLETELY DIFFERENT APPROACH.

Thanks, Johnnyboy1x

 

[barry houdini]

To keep the formulas relatively simple it would be easier to use a helper column, in E2 use this formula copied down

=SUBTOTAL(2,E2)

Then for a count that only considers visible values

=SUM(COUNTIFS(E:E,1,D:D,{">30","<-30"}))

You can hide column E if you want

 

[Johnnyboy1x]

Thank you for the quick reply Barry.

It is close but filtering still seems not to work.

Does this all seem right to you? I started my formulas in F1 so they are not in the data set. I changed the header of E to a number and everycell in E =SUBTOTAL(2,E1),

What am I missing?

status	serial	type	days from target	12345
Closed	123013/07	PM	1	1
Closed	128083/09	PM	35	1
Closed	136761/13	PM	35	1
Closed	141305/15	PM	35	1
Closed	137902/14	INST	1	1
Closed	133326/11	INST	1	1
Closed	125949/08	INST	33	1
Closed	141734/15	PM	-34	1
Closed	141734/15	PM	5	1
Closed	141734/15	PM	5	1

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

sum count if	count	percent on time
5	10	50%
=SUM(COUNTIFS(E:E,1,D:D,{">30","<-30"}))	=SUBTOTAL(2,D:D)	=(G2-F2)/G2

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Thanks, Johnnyboy1x

 

[Johnnyboy1x]

Hello Barry -- I got this to work but is cumbersome. Thanks for the support.

1.

sum product >30

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2:D1000,ROW(D2:D1000)-MIN(ROW(D2:D1000)),,1))*(D2:D1000>30)) 2. sum product <-30 =SUMPRODUCT(SUBTOTAL(3,OFFSET(D2:D1000,ROW(D2:D1000)-MIN(ROW(D2:D1000)),,1))*(D2:D1000<-30)) 3. sum of G and H =SUM(G2:H2) 4. visible count of D =SUBTOTAL(2,D:D) 5. percent on time =(J2-I2)/J2 Thanks, John <colgroup><col></colgroup><tbody> </tbody> <colgroup><col></colgroup><tbody> </tbody> <colgroup><col></colgroup><tbody> </tbody> <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>