|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
hlp with sumproduct!
yes Biff! i've mistakenly quoted as 1400 i/o 1200! -via135 Biff Wrote: 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 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=514671 |
| All times are GMT +1. The time now is 01:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com