Prev: Linking sumif formula to the data
Next: SUMPRODUCT
From: Scott on 6 May 2010 17:08
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 Opening
Stock'!C2:AH138=A7),'Client Opening Stock'!C2:X138)

'Client Opening Stock'!B2:B138 is the Client Name
Client Opening Stock'!C2:AH138 is the Product
'Client Opening Stock'!C2:X138 is the Amount

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:

On Thursday, February 25, 2010 11:21 PM
albertmb wrote:

Sumproduct Problem (AB)
Hi everyone, can you please sort out this problem for me?

SUMPRODUCT(('Client Opening Stock'!B2:B138=$A$1)*('Client Opening
Stock'!C2:AH138=A7),'Client Opening Stock'!C2:X138)

'Client Opening Stock'!B2:B138 is the Client Name
Client Opening Stock'!C2:AH138 is the Product
'Client Opening Stock'!C2:X138 is the Amount

I belive the formula is not the proper formula, but this is the result I need.

Thank you for your kind help

On Thursday, February 25, 2010 11:51 PM
Fred Smith wrote:

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 is
it "not the proper formula"? What is "the result I need"?

Regards,
Fred

On Friday, February 26, 2010 12:59 AM
albertmb wrote:

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 I
am getting for this formula is 'VALUE' so I assume there is something wrong
with the formula. I will try to explain better,

SUMPRODUCT(('Client Opening Stock'!B2:B138=$A$1)*('Client Opening
Stock'!C2:AH138=A7),'Client Opening Stock'!C2:X138)

'Client Opening Stock'!B2:B138=$A$1, (B2:B138) is the range where the client
names are listed and "A1" is where the client name is written on the result
sheet.

'Client Opening Stock'!C2:AH138=A7 (C2:AH138) is the range where the
Products are listed and 'A7' is where the product name is written on the
result sheet.

'Client Opening Stock'!C2:X138 is the Quantity of product that is associated
with the client name.

Thank You once again and I hope I was able to explain myself better

"Fred Smith" wrote:

On Friday, February 26, 2010 10:45 AM
Fred Smith wrote:

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 same
number of cells. Do you mean:
SUMPRODUCT(('Client Opening Stock'!B2:B138=$A$1)*('Client Opening
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/tutorials/aspnet/ff2d1d4b-aedf-4d14-9e60-39a86ccab5d6/using-vsto-addin-to-auto.aspx
 | 
Pages: 1
Prev: Linking sumif formula to the data
Next: SUMPRODUCT