Can I shorten this formula...

[apurk45]

I'm calculating range E4:F23. Currently I have this quite lengthy formula where if any cell in my range is empty, I 'm using "0" for my calculation:

=(IFERROR($E4*F4,"0")+IFERROR($E5*F5,"0")+IFERROR($E6*F6,"0")+IFERROR($E7*F7,"0")+IFERROR($E8*F8,"0")+IFERROR($E9*F9,"0")+IFERROR($E10*F10,"0")+IFERROR($E11*F11,"0")+IFERROR($E12*F12,"0")+IFERROR($E13*F13,"0")+IFERROR($E14*F14,"0")+IFERROR($E15*F15,"0")+IFERROR($E16*F16,"0")+IFERROR($E17*F17,"0")+IFERROR($E18*F185,"0")+IFERROR($E19*F19,"0")+IFERROR($E20*F20,"0")+IFERROR($E21*F21,"0")+IFERROR($E22*F22,"0")+IFERROR($E23*F23,"0"))/$E25

I have 2 questions:
1. Even though current formula does what I need, but can this be shortened?
2. Since some of the rows in my range can be hidden using filter, how can I change my formula so will calculate only data from the displayed rows. I was thinking about adding something like SUBTOTAL(9, ....... in front of my formula, but I have no idea how to do it.

Any help will be greatly appreciated.

Regards,
John

 

[Aladin Akyurek]

Try...

=IF(ISNUMBER(1/E25),SUMPRODUCT(E4:E23,F4:F23)/E25,0)

 

[apurk45]

Thank you Aladin

Works perfect.

I'm sorry to bug you, but I will also need some help with my 2nd question.
My E25 data point is calculated based only on displayed rows using =SUBTOTAL(9,E4:E23), but I have no idea how to modified your formula to work using data also only from displayed rows.

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(SUBTOTAL(2,OFFSET(E4,ROW(E4:E23)-ROW(E4),0),E4:E23,F4:F23)

Is this what you are looking for?

 

[apurk45]

Many thanks for your time.

When I used your original formula =IF(ISNUMBER(1/E25),SUMPRODUCT(E4:E23,F4:F23)/E25,0) the result was 95% - that is correct

When I used you modified formula =(SUMPRODUCT(SUBTOTAL(2,OFFSET(E4,ROW(E4:E23)-ROW(E4),0),E4:E23,F4:F23)))/E25 the answer was 65% - this is wrong

I'm not sure if that makes the difference, but some of the rows in my E4:E23 range are empty.

 

[Aladin Akyurek]

Many thanks for your time.

When I used your original formula =IF(ISNUMBER(1/E25),SUMPRODUCT(E4:E23,F4:F23)/E25,0) the result was 95% - that is correct

When I used you modified formula =(SUMPRODUCT(SUBTOTAL(2,OFFSET(E4,ROW(E4:E23)-ROW(E4),0),E4:E23,F4:F23)))/E25 the answer was 65% - this is wrong

I'm not sure if that makes the difference, but some of the rows in my E4:E23 range are empty.

So we have numbers in E4:F23.
We have also a number in E25.

The number E25 is based on a formula which is:

=SUBTOTAL(9,E4:E23)

It is not clear why we do a subtotal here that sums. Care to clarify?

You might even want to post the data in E4:F3 along the expected result.