Trying to combine SUBTOTAL function with an IF statement to show values that are % over/under the result of a SUBTOTAL median formula on filtered data

[moybelline]

Hi everyone,

I am very new to excel and am having trouble trying to do the following:

I have a subtotal function to show the median $$$ value of a number of cells, even when I filter.

I want to display a count of the number of rows where the $$$ value is under/over 70% of whatever the result of that median subtotal formula is.

Is this possible? I read I could use SUMPRODUCT but honestly can't get my head around how to make it work.

Any assistance would be much appreciated!

Thanks

 

[fadee2]

hi moybelline,
Welcome to the board,

use

subtotal(function_num, range)

shall give you total count once you activate the filter for 70%.

Alternatively you can use

countif(range, criteria)

to count total rows of 70% without placing the filter.

Hope that answers the question.