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


0
Utf
2/26/2010 4:21:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
725 Views

Similar Articles

[PageSpeed] 56

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

"albertmb" <albertmb@discussions.microsoft.com> wrote in message 
news:3C44E56A-0EAF-42ED-B478-4FE5E8E230F2@microsoft.com...
> 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
>
> 

0
Fred
2/26/2010 4:51:59 AM
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:

> 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
> 
> "albertmb" <albertmb@discussions.microsoft.com> wrote in message 
> news:3C44E56A-0EAF-42ED-B478-4FE5E8E230F2@microsoft.com...
> > 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
> >
> > 
> 
> .
> 
0
Utf
2/26/2010 5:59:01 AM
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

"albertmb" <albertmb@discussions.microsoft.com> wrote in message 
news:F2F1B73D-59B7-4FC8-B214-0A6129A2763E@microsoft.com...
> 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:
>
>> 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
>>
>> "albertmb" <albertmb@discussions.microsoft.com> wrote in message
>> news:3C44E56A-0EAF-42ED-B478-4FE5E8E230F2@microsoft.com...
>> > 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
>> >
>> >
>>
>> .
>> 

0
Fred
2/26/2010 3:45:50 PM
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
0
Scott
5/6/2010 9:08:21 PM
Reply:

Similar Artilces:

Problem adding address to Net Folder calender Outlook 2000 since SP 3
Hello, since I have updated to SP 3 with Outlook 2000, I am not able to add an address to my net folder calender. The button is still active but nothing happens if I press it. Does anybody has the same question and can help me? Thanks for the Reply. Best regards, Mathias Ulmer ...

problem in printing bitmap
How to print Bitmap? If we pass orignal width and height of the BMP in the BitBlt function it prints Bmp of very small size. After setting the map mode and logical coordinates also the image gets stretched. Can anybody help me in printing Bitmap with the correct size and image is also not stretched. I am not able to set the logical coordinates according to the printer. Can somebody can send the sample for printing bitmap. Thanks in advance Rajani Hope I have already answered this... "Ritu Chawla" <ritu.c@momentum-tech.com> wrote in message news:#rUZ#Z3$DHA.688@tk2msftngp13...

Filter problem
Hello, I have Exchange 2K set to forward bounces to a mailbox where I set filters using Outlook 2K. I have tried several times to set a filter to delete messages with a certain string or word in the body of the message to no avail. For example, the following text is common in bounces that come into the mailbox, and I would like to filter based on the string, "account does not exist", but the filter performs no action on the message as if the text wasn't actually in the body of the message. Anyone have any clue how to get this to work? Your message did not reach some or all o...

Outlook 2003 Continuing Hang Problem
I am running Outlook 2003 on WinXP Prof. and have had ongoing problems for weeks. Outlook loads and then hangs. Running Detect and Repair fixes the problem temporarily, as does renaming the outcmd.dat file, but after a few hours of functionality, the hang problem returns. I have completely uninstalled and then re-installed MS Office 2003 and the Outlook problem appeared to go away for a few days. Today it is back again and I am completely at a loss as what to do next. Any help would be greatly appreciated. Liz & Roger Try this: Download, install, update and scan with Ad-aware: http...

Do Not Deliver Before problem
We are running Exchange 2003. Previously with Outlook 2000 I could set the Do Not Deliver Before option and close outlook and log off the computer and Exchange would send my messages while I was gone. Now with Outlook 2003, the messages don't send until I open Outlook. I've made sure the Cached Folders options for my account are unchecked. Any other ideas? -- Lauri Bellingham Public Schools Application Support Are you in cached exchange mode? -- ~orko~ : MCDST "Lauri" wrote: > We are running Exchange 2003. > Previously with Outlook 2000 I could set the ...

Microsoft CRM
Hi, I have a hard problem with - license key inserting into MS CRM - and lanching new installed MS CRM system Problem <adding license key> Because I have had a <adding license key>, I obtain a hint do installation follow IG dokument. I have installed Windows2000, Exchange2000, SQLSrv2000 follow Implement Guide document. Because I would like to start sample aplication MSDN, I have reinstalled servers and change organization name to <adventure-works>. Now I am still problem with <adding license key> (designed in IG page 123). Problem with <running MSCRM> Af...

Excel VBA-vlookup combo box problem
Hi can someone please help me. I have created a worksheet in which i have Vlookup formulaes reading o another worksheet which has combobox with drop down lists and linke cells. Everything works apart from the comboboxes whose populate linked cells are not being recognised by the v-lookup function. Th Vlookup is as follows: vlookup(e18,'policy'!c5:098,7,false) The data within the combo box is a mixture of text and numerical e. L32H11-1, EM2H11-1 I have put a double minus sign in front of e18 and it returns #value! Can someone please help me. thanking you in anticipation Saik ...

Custom dictionary problems
Whenever I spell check an email, it tells me there is no custom dictionary and asks if I want to create one. Selecting Yes, gives an error that states and error occured while trying to create the custom dictionary. Anyone have a solution? Thanks, Jim Hi James, if the spellchecker wont work you have to look in Word options! -- Ich habe nichts gemacht, gestern gings noch! Bitte in den Newsgroup antworten damit jeder etwas davon hat. Bravestar@Datenschutzministerium.de "James" <jamesl70@hotmail.com> schrieb im Newsbeitrag news:Xns950CACF995074jamesl70hotmailcom@207.46.248...

Still problems
I cannot delete the pst files so I decided to completely delete outlook 2003 and then I did a reinstall hoping I could then start with the new account and it would be clean. However, when I did the install it could not find the ..pst file and would not open a new one. I had to restore it from my deleted files and now it is open again. How can I just delete the mail folders in Outlook and start clean??? With OL closed try the mail applet in the control panel to add a data file, set it as default, and remove old data file "Terri" <terri@mylanusa.com> wrote in message ...

Problem when registering OCX
This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C3EA6F.B5037D00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a problem that occurs when building an ActiveX OCX that uses MFC. = What happens is that after the OCX is linked, VS.NET 2003 attempts to = register the OCX via regsvr32 and fails with a very unhelpful message = stating... Project : error PRJ0019: A tool returned an error code from "Performing = registration" An important thing to note is that this ONLY happens when statically = linki...

HTML Problem with certain clients w/ Exchange 2003
I am having a very strange problem: HTML email that comes in is being somewhat garbled and not displayed properly on clients such as Outlook Express and Mac Clients (for example). Outlook (full) seems to be fine. Anyone know what's wrong? Is it the encoding? I have Windows 2003 EE w/ SP1 and Exchange 2003 with the latest SP. Mike, I was wondering if you ever got this resolved. I am experiencing the same issue "MikeZ" wrote: > I am having a very strange problem: HTML email that comes in is being > somewhat garbled and not displayed properly on clients such as Outlo...

Outlook Mail Item Duplicate "Display Name" Problem
Dear all, I'm currently writing a Persona Menu smart tag in Outlook 2003. I use dynamic caption and intend to modify the "Additional Actions" sub-menu depending on the contact(in the MAPIFolder) that the tag is referring to. Problem occurs in the mapping of the tag and OL contact. All information I can find from the smart tag action DLL(with ISmartTagAction2 interface) is just the tagged text string from get_VerbCaptionFromID2() but not the entity object that calls the persona menu. So my way to complete the chain is to : 1) search the sender and recipients' "display...

CRM 1.2 Installation problem.
Hi all, I read many topics here and met the same situations I am facing.But no resolutions're right for me. So I post a new one with my hope anyone which had solved the problem could help me. I installed Microsoft CRM 1.2. After the installation, restarted, I could see in the Event viewer : 1. Crystal APS started and stopped. The error : The root server reported an error Initialization Failure. (Reason: Unable to connect to the database using the provided connection string. Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login '_S_Software_Co...

problem sending
a client is having problems sending email from outlook. Some of the replies she sends out, when received, are missing text and instead is a line like this: << File: ATT00005.txt; charset = Windows-1252 >> What's causing this behavior, and how is it remedied? For example, she copied some text from an email I sent to her into outlook and then added some of her own plain text, and I received the plain text part, but instead of what she copied, I also received a line like that above. Thanks. -- Bill ...

Keyboard problem in Outlook 2003
I have my keyboard to UK English and the keyboard works fine in all my programs but Outlook 2003. For example if I do shift and 2 get a @ on the screen instead of a ". If I do shift and ' I get " on the screen and not a @. This only happens in Outlook, can anyone tell me why? Thanks Tony ...

Problems with archiving
hi! We have an Exchange Server 2003 on a Windows Server 2003 and our clients are using Outlook 2003. When I start archiving all folders are created in the PST-file. But the folders are empty! Why? Does someone know the answer? greetings m.neumeister Matthias Neumeister <matthias@neumeister.net> wrote: > We have an Exchange Server 2003 on a Windows Server 2003 and our > clients are using Outlook 2003. > When I start archiving all folders are created in the PST-file. But > the folders are empty! Why? Perhaps the modified dates on the items are more recent that the archi...

.DEF FIle Problem
i have many functions with the same name but different parameters in my extension dll. how can i specify those functions in a .DEF File, i mean how to differentiate. thanks for the help <anonymous@discussions.microsoft.com> wrote in message news:076501c3bed6$8af64520$a301280a@phx.gbl... > i have many functions with the same name but different > parameters in my extension dll. how can i specify those > functions in a .DEF File, i mean how to differentiate. > > thanks for the help > I am not sure why you need this DEF file as you know the reasons already. The only gu...

Release mode problems
Hi all, i am also facing rhe same problem, while compiling my code in Release mode Kind of hard to help when your description of the problem you are having only says it's the same. I'm sure this ties into another post somewhere... -- Jonathan Wood SoftCircuits Programming http://www.softcircuits.com <yendeti@gmail.com> wrote in message news:e05eb7fc-0385-4361-bbbf-08ec9ffc39a7@z17g2000hsg.googlegroups.com... > Hi all, > > i am also facing rhe same problem, while compiling my code in Release > mode Could you post a bit of your code or describe the problem a...

Another Problem
I keep getting these messages when I try to send to one of our contacts-- Your message did not reach some or all of the intended recipients. Subject: Test Sent: 8/3/2005 3:05 PM The following recipient(s) could not be reached: Contact, My on 8/3/2005 3:05 PM A configuration error in the e-mail system caused the message to bounce between two servers or to be forwarded between two recipients. Contact your administrator. <myemailserver.gilmanassembly.com #5.4.6> You have something screwed up in your routing. Does the contact have a target ...

Problems unzipping folders with photos
Help! I'm receivingattachments in my Outlook email program with our new grandbaby pictures, but the folders are zipped. I tried extracting also. When I double click on the folders to open them, there is nothing inside. Can someone help me. southern belle <anonymous@discussions.microsoft.com> wrote: > Help! I'm receivingattachments in my Outlook email program with our > new grandbaby pictures, but the folders are zipped. I tried > extracting also. What happened when you saved the Zip file to a folder and then tried to open it? What version of Windows are you using? ...

Help:smart host problem
From: "Damon" <Damon@telus.net> Subject: Date: Friday, April 20, 2007 9:40 PM Hi, now I use SMTP.gmail.com as my smart host. My email is like usename@ mydomian.com. All the send-out email from my exchange server (mydomain.com) by smarthost-SMTP. Gmail.com is marked 'from damonXXXX@ GMAIL.com'! How to set it properly? Thank you.... "Damon" <Damon@telus.net> wrote: >Hi, now I use SMTP.gmail.com as my smart host. >My email is like usename@ mydomian.com. >All the send-out email from my exchange server (mydomain.com) by >smarthost-SMTP...

Stacked Bar Legend problem
The table of data for my chart has names down the side and CBT Groups across the top. The data in the middle is number of CBTs completed by each person. Something like this: Name Winx OS10 MCP A+ Bill 5 0 1 0 Jim 2 1 0 0 Jane 7 0 1 0 Lacy 1 0 0 1 Marvin 3 1 1 0 I want to create a stack bar chart that has names as the X axis. The bar for each name will have within it stacks of CBTs they have completed. I'm able to create the chart but I can not get the CBT Group titles to appear in the Legend. The ...

Content library problem #2
Hi all - Publisher 2007. I write a newsletter twice a year. Since I use many of the same elements on the back page every time, I erased the volatile areas, then did a Save All on the page, and added it to the Content Library. But when I bring the page back in from the Content Library to a new, blank page, the paragraph spacing on the paragraphs has changed and left me with text boxes in an overflow condition. Am I doing something wrong? Craig ...

A problem with IMS instalation / start
Hi. I had a problem with my Exchange 5.5 server as IMS won=B4t=20 start. I've tryed everthing (or almost) with no success.=20 Then I removed all exchange and later I re-installed it.=20 But I get the same problem when I try to install IMS... I=20 don't know what is happening as IMS simpply doesn't start=20 after instalation. I cannot have a complete e-mail system=20 without this IMS. Appreciate any help. Regards, S=E9rgio Mapsanganhe What errors do you get after trying to start the IMS? Does the install complete without any errors? What errors are there in the application logs...

Excel 2003 VBA problem
Never used R1C1 notation before and I;m not certain that I understand it. The routine below posts a copy of "ProjectID" to the next available line but am unable to get the VBA macro to save with the R1C2 and R1C3 entries. VBA does not like this - all I was trying to do was post the three input entries in the first available row in the first three columns. Can anyone help or at least point me in the right direction Thanks BJthebear Sub InputNewproject() ' ' InputNewUser Macro ' Macro recorded 01/04/2010 by Brian ' Dim NewprojectID As String &#...