Your formula contains some overlapping ranges I don't think were intended.
Please consider the following:
Client 2 Product 2
Client Product Amount
Client 1 Product 1 1
Client 2 Product 2 2
Client 3 Product 3 3
Client 4 Product 4 4
Client 5 Product 5 5
Client 6 Product 6 6
Client 7 Product 7 7
Client 8 Product 8 8
Client 9 Product 9 9
Client 10 Product 10 10
Client 11 Product 11 11
=SUMPRODUCT((A4:A14=$A$1)*(B4:B14=$B$1),C4:C14) = 2
The intersecting "Amount" for your given Client and Product if and only if the two selections happen to occur on the same line.
Is this the intended effect?
albertmb wrote:
Sumproduct Problem (AB)
25-Feb-10
Hi everyone, can you please sort out this problem for me
SUMPRODUCT(('Client Opening Stock'!B2:B138=$A$1)*('Client Openin
Stock'!C2:AH138=A7),'Client Opening Stock'!C2:X138
'Client Opening Stock'!B2:B138 is the Client Nam
Client Opening Stock'!C2:AH138 is the Produc
'Client Opening Stock'!C2:X138 is the Amoun
I belive the formula is not the proper formula, but this is the result I need
Thank you for your kind help
Previous Posts In This Thread:
Sumproduct Problem (AB)
Hi everyone, can you please sort out this problem for me
SUMPRODUCT(('Client Opening Stock'!B2:B138=$A$1)*('Client Openin
Stock'!C2:AH138=A7),'Client Opening Stock'!C2:X138
'Client Opening Stock'!B2:B138 is the Client Nam
Client Opening Stock'!C2:AH138 is the Produc
'Client Opening Stock'!C2:X138 is the Amoun
I belive the formula is not the proper formula, but this is the result I need
Thank you for your kind help
You need to tell us what the problem is, and what solution you want.
You need to tell us what the problem is, and what solution you want. Why i
it "not the proper formula"? What is "the result I need"
Regards
Fred
Hi Fred,Thank you for responding and I apologise if I was not clear.
Hi Fred
Thank you for responding and I apologise if I was not clear. The result
am getting for this formula is 'VALUE' so I assume there is something wron
with the formula. I will try to explain better
SUMPRODUCT(('Client Opening Stock'!B2:B138=$A$1)*('Client Openin
Stock'!C2:AH138=A7),'Client Opening Stock'!C2:X138
'Client Opening Stock'!B2:B138=$A$1, (B2:B138) is the range where the clien
names are listed and "A1" is where the client name is written on the resul
sheet
'Client Opening Stock'!C2:AH138=A7 (C2:AH138) is the range where th
Products are listed and 'A7' is where the product name is written on th
result sheet
'Client Opening Stock'!C2:X138 is the Quantity of product that is associate
with the client name
Thank You once again and I hope I was able to explain myself bette
:
Your problem is that all ranges in a Sumproduct have to contain the samenumber
Your problem is that all ranges in a Sumproduct have to contain the sam
number of cells. Do you mean
SUMPRODUCT(('Client Opening Stock'!B2:B138=$A$1)*('Client Openin
Stock'!C2:C138=A7),'Client Opening Stock'!X2:X138
Regards
Fred
Submitted via EggHeadCafe - Software Developer Portal of Choice
Using VSTO Add-In To Automate Frequent Excel 2007 Tasks
http://www.eggheadcafe.com/tutorial...60-39a86ccab5d6/using-vsto-addin-to-auto.aspx