ExcelBanter - How do I Clean up this formula?

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - How do I Clean up this formula? (https://www.excelbanter.com/excel-programming/271563-how-do-i-clean-up-formula.html)

Billy Two Hats

How do I Clean up this formula?

SUMIF(J7:J18,Sheets1!E4,G7:G18)*Sheets1!H4%+SUMIF( J7:J18,Sheets1!E4,H7:H18)*
Sheets1!H4%+ etc

The "etc" is the formula repeated over with the only change being to the row
number i.e. in E&H, 4.
The 4 become 29 and then 56.

Thanks
Dave

J.E. McGimpsey

How do I Clean up this formula?

One way, though I suspect it's not optimal:

=SUMPRODUCT(((J7:J18=Sheets1!E4)*Sheets1!H4+(J7:J1 8=Sheets1!E29) *
Sheets1!H29+(J7:J18=Sheets1!E56)*Sheets1!H56)%*(G7 :H18))

In article ,
"Billy Two Hats" wrote:

SUMIF(J7:J18,Sheets1!E4,G7:G18)*Sheets1!H4%+SUMIF( J7:J18,Sheets1!E4,H7:H18)*
Sheets1!H4%+ etc

The "etc" is the formula repeated over with the only change being to the row
number i.e. in E&H, 4.
The 4 become 29 and then 56.

Thanks
Dave

All times are GMT +1. The time now is 05:12 AM.