Simplified SUMPRODUCT formula !

[wilchong via OfficeKB.com]

Dear sir,
I have a problem regarding the SUMPRODUCT function. I used to add some
numbers from number of worksheets such as WK1, WK2, WK3 etc. As a result, my
formula become very long as following:
=SUMPRODUCT((WK1!$E$2:$E$100=$D8)*(G$3<WK1!$B$2:$B$100)*(WK1!$B$2:$B$100<=H$3)
,(WK1!$D$2:$D$100))+SUMPRODUCT((WK2!$E$2:$E$100=$D8)*(G$3<WK2!$B$2:$B$100)*
(WK2!$B$2:$B$100<=H$3),(WK2!$D$2:$D$100))+SUMPRODUCT((WK3!$E$23:$E$100=$D8)*
(G$3<WK3!$B$2:$B$100)*(WK3!$B$2:$B$100<=H$3),(WK3!$D$2:$D$100))

My question is that is there any way to have one formula which can perform
the same function, just like this example, instead of make such long formula
like this:

=SUM('Worksheet 2'!A1:A10)+SUM('Worksheet 3'!A1:A10), I can have this more
shorten formula to replace the long one: =SUM('Worksheet 2:Worksheet 3'!A1:
A10).

Please advice.
Wilchong

 

[Bernard Liengme]

Why not put a simple formula on each worksheet. So on WK1 in cell Z1 enter
=SUMPRODUCT(($E$2:$E$100=Main!$D8)*(Main!G$3<$B$2:$B$100)*($B$2:$B$100<=Main!H$3),($D$2:$D$100))If you group the WK worksheets as you enter this, you should be able toenter it into all WK sheetsThen in Main use =SUM(WK1:WK3!Z1)--Bernard V LiengmeMicrosoft Excel MVPhttp://people.stfx.ca/bliengmeremove caps from email"wilchong via OfficeKB.com" <u43231@uwe> wrote in messageDear sir,> I have a problem regarding the SUMPRODUCT function. I used to add some> numbers from number of worksheets such as WK1, WK2, WK3 etc. As a result,my> formula become very long as following:>=SUMPRODUCT((WK1!$E$2:$E$100=$D8)*(G$3<WK1!$B$2:$B$100)*(WK1!$B$2:$B$100<=H$3)>,(WK1!$D$2:$D$100))+SUMPRODUCT((WK2!$E$2:$E$100=$D8)*(G$3<WK2!$B$2:$B$100)*>(WK2!$B$2:$B$100<=H$3),(WK2!$D$2:$D$100))+SUMPRODUCT((WK3!$E$23:$E$100=$D8)*> (G$3<WK3!$B$2:$B$100)*(WK3!$B$2:$B$100<=H$3),(WK3!$D$2:$D$100))>> My question is that is there any way to have one formula which can perform> the same function, just like this example, instead of make such longformula> like this:>> =SUM('Worksheet 2'!A1:A10)+SUM('Worksheet 3'!A1:A10), I can have this more> shorten formula to replace the long one: =SUM('Worksheet 2:Worksheet3'!A1:> A10).>> Please advice.> Wilchong>> --> Message posted via http://www.officekb.com>

 

[wilchong via OfficeKB.com]

Dear Bernard Liengme,
May be I didn't explain carefully regarding the situation. In the WK1, WK 2
and WK 3, column B shows the date data, column E shows description data and
column D shows amount. In another worksheet, I have the SUMPRODUCT to
analyse more than 100 descriptions from three worksheets (WK1, WK 2 and WK 3).
In the real situation, there are more than 10 worksheets to sumup the amount
under different date and different description.

I really wish I can a way to motify or shorten the SUMPRODUCT function which
can cover three worksheets. I hope you can advice me on this. Many thanks,
Wilchong

 

[Bernard Liengme]

I can only repeat: it is simpler to find the data for each worksheet - even
if you have 100 formulas on each sheet. Do one and copy the formulas to the
next, or group the sheets as you type the formula. These formula can be far
off to the right where nobody 'visits'; they can even be hidden.
Then on the summary sheet add all the values for the individual sheets.
best wishes