Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simplified SUMPRODUCT formula !
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=$D 8)*(G$3<WK2!$B$2:$B$100)* (WK2!$B$2:$B$100<=H$3),(WK2!$D$2:$D$100))+SUMPRODU CT((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 -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simplified SUMPRODUCT formula !
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 messagenews:91e1f0be4b613@uwe... 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<WK 2!$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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simplified SUMPRODUCT formula !
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 wrote: Why not put a simple formula on each worksheet. So on WK1 in cell Z1 enter -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200902/1 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simplified SUMPRODUCT formula !
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:91eb7349154be@uwe... 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 wrote: Why not put a simple formula on each worksheet. So on WK1 in cell Z1 enter -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200902/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
working around errors - simplified | Excel Discussion (Misc queries) | |||
Simplified Date Entry? | Excel Discussion (Misc queries) | |||
looking for simplified formulas | Excel Worksheet Functions | |||
Simplified Data Entry | Excel Discussion (Misc queries) | |||
Can this be simplified | Excel Worksheet Functions |