|
Asking for formula~thanks!
If I want to do the belows, what can I do?
Sheet 1 A B C 1 Com 1 Aug 100 2 Com 2 Aug 100 3 Com 2 Sept 120 4 Com 2 Oct 111 5 Com 1 Sept 111 6 Com 3 Oct 130 7 Com 4 Aug 130 : : : : : : Sheet 2 A B C D 1 Aug Sept Oct ........... 2 Com 1 3 Com 2 4 Com 3 5 Com 4 If I want to sum up the number in sheet 2 of Com 1 in Aug, Com 2 in Aug, Com 3 in Aug.............. Com 4 in Oct....etc. What formula can i use? Thank a lots! |
Hi Summer,
In the cell [B2] of Sheet 2 write the following formula and copy it down and to the right: for sum of values in column [C]: =SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1),Sheet1!$C$1:$C$10) for counts: =SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1)) Regards, KL "Summer" wrote in message ... If I want to do the belows, what can I do? Sheet 1 A B C 1 Com 1 Aug 100 2 Com 2 Aug 100 3 Com 2 Sept 120 4 Com 2 Oct 111 5 Com 1 Sept 111 6 Com 3 Oct 130 7 Com 4 Aug 130 : : : : : : Sheet 2 A B C D 1 Aug Sept Oct ........... 2 Com 1 3 Com 2 4 Com 3 5 Com 4 If I want to sum up the number in sheet 2 of Com 1 in Aug, Com 2 in Aug, Com 3 in Aug.............. Com 4 in Oct....etc. What formula can i use? Thank a lots! |
Dear KL
Thank a lots! The formula is very useful, however, when I have new inputs in sheet 1, sometimes it couldn' t show in sheet 2, I have to retype again and again to make it show in sheet 2. "KL" wrote: Hi Summer, In the cell [B2] of Sheet 2 write the following formula and copy it down and to the right: for sum of values in column [C]: =SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1),Sheet1!$C$1:$C$10) for counts: =SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1)) Regards, KL "Summer" wrote in message ... If I want to do the belows, what can I do? Sheet 1 A B C 1 Com 1 Aug 100 2 Com 2 Aug 100 3 Com 2 Sept 120 4 Com 2 Oct 111 5 Com 1 Sept 111 6 Com 3 Oct 130 7 Com 4 Aug 130 : : : : : : Sheet 2 A B C D 1 Aug Sept Oct ........... 2 Com 1 3 Com 2 4 Com 3 5 Com 4 If I want to sum up the number in sheet 2 of Com 1 in Aug, Com 2 in Aug, Com 3 in Aug.............. Com 4 in Oct....etc. What formula can i use? Thank a lots! |
Hi Summer,
You can... 1) Make the range big enough to capture future additions: =SUMPRODUCT(--(Sheet1!$A$1:$A$1000=$A2),--(Sheet1!$B$1:$B$1000=B$1),Sheet1!$C$1:$C$1000) 2) Create a dinamic named range. - go to menu InsertNameDefine - write "rng" (without quotation marks) inthe 'Name' box - copy the following formula into the 'Refers to:' box =Sheet1!$A$2:INDEX(Sheet1!$C:$C,MATCH(REPT("z",255 ),Sheet1!$A:$A)) - press Add and then OK buttons change your formula as follows: =SUMPRODUCT(--(INDEX(rng,,1)=$A2),--(INDEX(rng,,2)=B$1),INDEX(rng,,3)) Regards, KL "Summer" wrote in message ... Dear KL Thank a lots! The formula is very useful, however, when I have new inputs in sheet 1, sometimes it couldn' t show in sheet 2, I have to retype again and again to make it show in sheet 2. "KL" wrote: Hi Summer, In the cell [B2] of Sheet 2 write the following formula and copy it down and to the right: for sum of values in column [C]: =SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1),Sheet1!$C$1:$C$10) for counts: =SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1)) Regards, KL "Summer" wrote in message ... If I want to do the belows, what can I do? Sheet 1 A B C 1 Com 1 Aug 100 2 Com 2 Aug 100 3 Com 2 Sept 120 4 Com 2 Oct 111 5 Com 1 Sept 111 6 Com 3 Oct 130 7 Com 4 Aug 130 : : : : : : Sheet 2 A B C D 1 Aug Sept Oct ........... 2 Com 1 3 Com 2 4 Com 3 5 Com 4 If I want to sum up the number in sheet 2 of Com 1 in Aug, Com 2 in Aug, Com 3 in Aug.............. Com 4 in Oct....etc. What formula can i use? Thank a lots! |
Or, if you are on Excel 2003, convert the current data area into a LIST
by means of Data|List|Create List. After this, you don't have to edit the SumProduct formula for it will be automatically adjusted for ranges the formula refers to. KL wrote: Hi Summer, You can... 1) Make the range big enough to capture future additions: =SUMPRODUCT(--(Sheet1!$A$1:$A$1000=$A2),--(Sheet1!$B$1:$B$1000=B$1),Sheet1!$C$1:$C$1000) 2) Create a dinamic named range. - go to menu InsertNameDefine - write "rng" (without quotation marks) inthe 'Name' box - copy the following formula into the 'Refers to:' box =Sheet1!$A$2:INDEX(Sheet1!$C:$C,MATCH(REPT("z",255 ),Sheet1!$A:$A)) - press Add and then OK buttons change your formula as follows: =SUMPRODUCT(--(INDEX(rng,,1)=$A2),--(INDEX(rng,,2)=B$1),INDEX(rng,,3)) Regards, KL "Summer" wrote in message ... Dear KL Thank a lots! The formula is very useful, however, when I have new inputs in sheet 1, sometimes it couldn' t show in sheet 2, I have to retype again and again to make it show in sheet 2. "KL" wrote: Hi Summer, In the cell [B2] of Sheet 2 write the following formula and copy it down and to the right: for sum of values in column [C]: =SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1),Sheet1!$C$1:$C$10) for counts: =SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1)) Regards, KL "Summer" wrote in message ... If I want to do the belows, what can I do? Sheet 1 A B C 1 Com 1 Aug 100 2 Com 2 Aug 100 3 Com 2 Sept 120 4 Com 2 Oct 111 5 Com 1 Sept 111 6 Com 3 Oct 130 7 Com 4 Aug 130 : : : : : : Sheet 2 A B C D 1 Aug Sept Oct ........... 2 Com 1 3 Com 2 4 Com 3 5 Com 4 If I want to sum up the number in sheet 2 of Com 1 in Aug, Com 2 in Aug, Com 3 in Aug.............. Com 4 in Oct....etc. What formula can i use? Thank a lots! -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
| All times are GMT +1. The time now is 02:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com