| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
hlp with sumproduct!
hi! what's wrong with the following formula which throws #VALUE error? =SUMPRODUCT(--(H1:H5,H1)*(--(Sheet7!H1:H5,H1)),(--(I1:I5),(Sheet7!I1:I5))) -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514671 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
hlp with sumproduct!
Hi!
Try this: =SUMIF(H1:H5,H1,I1:I5)+SUMIF(Sheet7!H1:H5,H1,Sheet 7!I1:I5) Biff "via135" wrote in message ... hi! what's wrong with the following formula which throws #VALUE error? =SUMPRODUCT(--(H1:H5,H1)*(--(Sheet7!H1:H5,H1)),(--(I1:I5),(Sheet7!I1:I5))) -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514671 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
hlp with sumproduct!
thks Biff! your formula works nicely!! can u pl explain why "someproduct" doesn't work for this situation? -via135 Biff Wrote: Hi! Try this: =SUMIF(H1:H5,H1,I1:I5)+SUMIF(Sheet7!H1:H5,H1,Sheet 7!I1:I5) Biff "via135" wrote in message ... hi! what's wrong with the following formula which throws #VALUE error? =SUMPRODUCT(--(H1:H5,H1)*(--(Sheet7!H1:H5,H1)),(--(I1:I5),(Sheet7!I1:I5))) -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514671 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514671 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
hlp with sumproduct!
Hi!
I was guessing that the Sumif is what you intended. You had the syntax of the Sumproduct formula wrong and Sumproduct wouldn't do what you had intended to do. See Arvi's reply. Biff "via135" wrote in message ... thks Biff! your formula works nicely!! can u pl explain why "someproduct" doesn't work for this situation? -via135 Biff Wrote: Hi! Try this: =SUMIF(H1:H5,H1,I1:I5)+SUMIF(Sheet7!H1:H5,H1,Sheet 7!I1:I5) Biff "via135" wrote in message ... hi! what's wrong with the following formula which throws #VALUE error? =SUMPRODUCT(--(H1:H5,H1)*(--(Sheet7!H1:H5,H1)),(--(I1:I5),(Sheet7!I1:I5))) -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514671 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514671 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
hlp with sumproduct!
hi Biff! i am stressing on SUMPRODUCT! Arvi's formula gives me a wrong result for my following data! Sheet7!H1:I5 CDA 10 EFG 30 ABC 40 BCD 50 ABC 60 Sheet8!H1:I5 ABC 10 BCD 50 ABC 30 CDA 70 ACB 60 i want the sum of all "ABC"! while your SUMIF gives me the correct result of 140 Aarvi's SUMPRODUCT gives me 1400 as he explained it! my point is whether it is possible to bring the result of 140 using "sumproduct"? thks! -via135 Biff Wrote: Hi! I was guessing that the Sumif is what you intended. You had the syntax of the Sumproduct formula wrong and Sumproduct wouldn't do what you had intended to do. See Arvi's reply. Biff "via135" wrote in message ... thks Biff! your formula works nicely!! can u pl explain why "someproduct" doesn't work for this situation? -via135 Biff Wrote: Hi! Try this: =SUMIF(H1:H5,H1,I1:I5)+SUMIF(Sheet7!H1:H5,H1,Sheet 7!I1:I5) Biff "via135" wrote in message ... hi! what's wrong with the following formula which throws #VALUE error? =SUMPRODUCT(--(H1:H5,H1)*(--(Sheet7!H1:H5,H1)),(--(I1:I5),(Sheet7!I1:I5))) -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514671 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514671 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514671 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
hlp with sumproduct!
Hi!
Aarvi's SUMPRODUCT gives me 1400 as he explained it! Using your data I get 1200. my point is whether it is possible to bring the result of 140 using "sumproduct"? No. Take a look at this screencap: http://img60.imageshack.us/img60/9403/sump6sp.jpg Biff "via135" wrote in message ... hi Biff! i am stressing on SUMPRODUCT! Arvi's formula gives me a wrong result for my following data! Sheet7!H1:I5 CDA 10 EFG 30 ABC 40 BCD 50 ABC 60 Sheet8!H1:I5 ABC 10 BCD 50 ABC 30 CDA 70 ACB 60 i want the sum of all "ABC"! while your SUMIF gives me the correct result of 140 Aarvi's SUMPRODUCT gives me 1400 as he explained it! my point is whether it is possible to bring the result of 140 using "sumproduct"? thks! -via135 Biff Wrote: Hi! I was guessing that the Sumif is what you intended. You had the syntax of the Sumproduct formula wrong and Sumproduct wouldn't do what you had intended to do. See Arvi's reply. Biff "via135" wrote in message ... thks Biff! your formula works nicely!! can u pl explain why "someproduct" doesn't work for this situation? -via135 Biff Wrote: Hi! Try this: =SUMIF(H1:H5,H1,I1:I5)+SUMIF(Sheet7!H1:H5,H1,Sheet 7!I1:I5) Biff "via135" wrote in message ... hi! what's wrong with the following formula which throws #VALUE error? =SUMPRODUCT(--(H1:H5,H1)*(--(Sheet7!H1:H5,H1)),(--(I1:I5),(Sheet7!I1:I5))) -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514671 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514671 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514671 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
hlp with sumproduct!
Hi
Probably the main reason is --(Range,Value) part - there must be --(Range=Value) instead. Additionally you use 2 different syntaxis at same time - it doesn't cause an error, but you don't gain anything too. You can have SUMPRODUCT or in form =SUMPRODUCT((Range1=Value2)*(Range2=Value2)*...*(R angeN)) or in form =SUMPRODUCT(--(Range1=Value2),--(Range2=Value2),...,RangeN) So, your formula probably will be =SUMPRODUCT(--(H1:H5=H1),--(Sheet7!H1:H5=H1)),I1:I5,Sheet7!I1:I5) The formula will be multiply row-wise values in ranges I1:I5 and Sheet7!I1:I5 and, when according values in both ranges H1:H5 and Sheet7!H1:H5 equal with value in H1, and sum then all multiplied values. When this is not what you intented, then you have to ask again. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "via135" wrote in message ... hi! what's wrong with the following formula which throws #VALUE error? =SUMPRODUCT(--(H1:H5,H1)*(--(Sheet7!H1:H5,H1)),(--(I1:I5),(Sheet7!I1:I5))) -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514671 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct | Excel Worksheet Functions | |||
| sumproduct causing memory errors? | Excel Worksheet Functions | |||
| Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
| Sumproduct function not working | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Hybrid Mode
