Confused: SUMIF combined with SUBTOTAL?

[kingofcamden]

Dear all,


I am trying to combine SUMIF & AVERAGE with SUBTOTAL to only "SUMIF/AVERAGEIF" visible cells. The formula I use for the SUMIF is:

=SUMIF(Report_Details!AA14:AA8013,1,Report_Details!F14:F8013)

And for the AVERAGEIF:

=AVERAGEIF(Report_Details!AA14:AA8013,1,Report_Details!H14:H8013)

However, as soon as I apply a filter to the data stored in the Report_Details sheet, the formulas don't update to only calculate the visible cells.


Does anyone know how this can be solved? Quite complex and I've tried for a while now with now success


Hope someone can help.


Many thanks,
Mat

 

[pgc01]

Hi Mat

Try:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Report_Details!AA14:AA8013,ROW(Report_Details!AA14:AA8013)-MIN(ROW(Report_Details!AA14:AA8013)),,1)),--(Report_Details!AA14:AA8013=1),Report_Details!F14:F8013)

 

[XOR LX]

Hi.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Report_Details!AA14,ROW(Report_Details!AA14:AA8013)-MIN(ROW(Report_Details!AA14:AA8013)),)),0+(Report_Details!AA14:AA8013=1),Report_Details!F14:F8013)

and, array formula** this one:

=AVERAGE(IF(SUBTOTAL(3,OFFSET(Report_Details!AA14,ROW(Report_Details!AA14:AA8013)-MIN(ROW(Report_Details!AA14:AA8013)),)),IF(Report_Details!AA14:AA8013=1,Report_Details!F14:F8013)))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[kingofcamden]

Hiya!

WOW That's what I call a formula!
Thanks so much - they both work great!!

One last question...hope you can also help with this one. Some of the results come out as #DIV/O! - is there any chance Excel can display a 0 in this case?

Thanks so much!!!
Cheers,
mat

 

[Aladin Akyurek]

1. Just enter:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Report_Details!F14,
    ROW(Report_Details!F14:F8013)-ROW(Report_Details!F14),0,1)),
    --(Report_Details!AA14:AA8013=1))

2. Control+shift+enter, not just enter:

=AVERAGE(IF(SUBTOTAL(2,OFFSET(Report_Details!F14,
    ROW(Report_Details!F14:F8013)-ROW(Report_Details!F14),0,1)),
    IF(Report_Details!AA14:AA8013=1,Report_Details!F14:F8013)))

 

[Aladin Akyurek]

[...]
One last question...hope you can also help with this one. Some of the results come out as #DIV/O! - is there any chance Excel can display a 0 in this case?
[...]

Try to wrap the AVERAGE formula into an IFERROR call...

=IFERROR(AVERAGE(...),0)

followed again by control+shift+enter, not just enter.

 

[kingofcamden]

Perfect! That did the trick Thanks so much!

Have a great day,
Mat

 

[kingofcamden]

Hiya,

I'm so sorry - another stupid question

Would it be possible to display all values that equal 100% as value Settings!$C$5 within this formula:

=IF(M14=Settings!$C$5,Settings!$C$5,SUM(100/G14*M14)/100)

? Try this with the IF function, but doesn't seem to work...

This this should be the last one for today

Thanks,
Mat

 

[Aladin Akyurek]

Hiya,

I'm so sorry - another stupid question

Would it be possible to display all values that equal 100% as value Settings!$C$5 within this formula:

=IF(M14=Settings!$C$5,Settings!$C$5,SUM(100/G14*M14)/100)

? Try this with the IF function, but doesn't seem to work...

This this should be the last one for today

Thanks,
Mat

Could you elaborate?

 

[kingofcamden]

Sure

I'm trying to exclude values larger than 99% from the results of this formula:

[COLOR=#574123]=IF(M14=Settings!$C$5,Settings!$C$5,SUM(100/G14*M14)/100)[/COLOR]

Sometimes I have results higher than 99% and in this case I would like to display the content of cell Settings!$C$5 (which is basically just text saying "Data above 99%").

So basically saying If the value is more than 99% display Settings!$C$5, if not do this: (M14=Settings!$C$5,Settings!$C$5,SUM(100/G14*M14)/100)

Hope you can help...

Many thanks,
Mat