subtotal on sumif

[BrutalLogiC]

Hi I'm trying to use a filter on a number of cells and want the value to update, I originally had =SUMIF(BJ8:BJ202,"Yes",O8:O202)

I have this formula but it returns zero value, any idea what is wrong?
=SUMPRODUCT(SUBTOTAL(9,OFFSET(BJ8,ROW(BJ8:BJ202)-ROW(BJ8),0)),(O8:O202="Yes")+0)

Any help appreciated

 

[Aladin Akyurek]

The formula is ok.

What is the result you get if you lift filtering?

 

[BrutalLogiC]

slightly changed as moved some columns about but it returns 0 filtered or unfiltered

=SUMPRODUCT(SUBTOTAL(9,OFFSET(AZ8,ROW(AZ8:AZ202)-ROW(AZ8),0)),(K8:K202="Yes")+0) returns 0 value


=SUMIF(AZ8:AZ202,"Yes",K8:K202) returns 38 million

 

[BrutalLogiC]

AZ is column with YES or NO......... when it's YES I want it to total the value in column K... and let me filter it also!

help!

 

[Aladin Akyurek]

AZ is column with YES or NO......... when it's YES I want it to total the value in column K... and let me filter it also!

help!

Looks like you meant:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(K8,ROW(K8:K202)-ROW(K8),0)),(AZ8:AZ202="Yes")+0)