SUMPRODUCT help #3

Help please

I'm trying to count the number on items sold on a particular month
using SUMPRODUCT but I'm getting the wrong answer

This is what I use for each month (Feburary) for this example:

=SUMPRODUCT((MONTH($L$2:$L$5973)=2)*(YEAR($L$2:$L$5973)=2007)*($U$2:$U$5973=$A4))

The item is in A4 the date is in column L and in Column U are all the
mixed items.
I have checked the date format and its ok.

Can any one help please?

Thanks
josa

0
jc2426 (4)
1/24/2007 1:58:58 PM
excel 39879 articles. 2 followers. Follow

3 Replies
348 Views

Similar Articles

[PageSpeed] 59

Odd. I would begin by testing each part of the formula. For example:
1) =SUMPRODUCT((MONTH($L$2:$L$5973)=2))
2) =SUMPRODUCT((YEAR($L$2:$L$5973)=2007))
3) =SUMPRODUCT((MONTH($L$2:$L$5973)=2)*(YEAR($L$2:$L$5973)=2007))
etc
best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JOSA" <jc2426@hotmail.com> wrote in message 
news:1169647138.412441.55900@m58g2000cwm.googlegroups.com...
> Help please
>
> I'm trying to count the number on items sold on a particular month
> using SUMPRODUCT but I'm getting the wrong answer
>
> This is what I use for each month (Feburary) for this example:
>
> =SUMPRODUCT((MONTH($L$2:$L$5973)=2)*(YEAR($L$2:$L$5973)=2007)*($U$2:$U$5973=$A4))
>
> The item is in A4 the date is in column L and in Column U are all the
> mixed items.
> I have checked the date format and its ok.
>
> Can any one help please?
>
> Thanks
> josa
> 


0
bliengme5824 (3040)
1/24/2007 2:28:30 PM
Josa,

Some possibilities:

THe dates in column L aren't really dates.  In a cell, try =MONTH(L55) to 
see if you get the correct month digit for that date.

The item in A4 doesn't exactly match anything in column U.  Try =A4=L55 
(change L55 to the correct cell).  You should see TRUE.

-- 
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"JOSA" <jc2426@hotmail.com> wrote in message 
news:1169647138.412441.55900@m58g2000cwm.googlegroups.com...
> Help please
>
> I'm trying to count the number on items sold on a particular month
> using SUMPRODUCT but I'm getting the wrong answer
>
> This is what I use for each month (Feburary) for this example:
>
> =SUMPRODUCT((MONTH($L$2:$L$5973)=2)*(YEAR($L$2:$L$5973)=2007)*($U$2:$U$5973=$A4))
>
> The item is in A4 the date is in column L and in Column U are all the
> mixed items.
> I have checked the date format and its ok.
>
> Can any one help please?
>
> Thanks
> josa
> 


0
someone798 (944)
1/24/2007 6:25:51 PM
"JOSA" <jc2...@hotmail.com> wrote...
>I'm trying to count the number on items sold on a particular month
>using SUMPRODUCT but I'm getting the wrong answer

Wrong how? Does the formula returns an incorrect though numeric result
or an error value? If an incorrect numeric result, is it zero or a
positive number less than what it should be?

>This is what I use for each month (Feburary) for this example:
>
>=SUMPRODUCT((MONTH($L$2:$L$5973)=2)*(YEAR($L$2:$L$5973)=2007)
>*($U$2:$U$5973=$A4))
....

Not that this is a fix, but you don't need separate month and year
checks. You could try

=SUMPRODUCT((TEXT($L$2:$L$5973,"yyyymm")="200702")*($U$2:$U$5973=$A4))

or (getting tricky and obscure but also more efficient)

=SUMPRODUCT((ABS($L$2:$L$5973-"2007-02-14"-0.5)<14)*($U$2:$U$5973=$A4))

Does the following formula return what you believe should be the
correct total number of items in column U?

=COUNTIF($U$2:$U$5973,$A4)

0
hrlngrv (1990)
1/24/2007 7:02:50 PM
Reply:

Similar Artilces:

Help with Excel
I have windows xp on my new computer and it don't support excel, i have file on my old computer with excel how can i get excel to work with my xp version You will need to put Excel on your new machine - it is NOT part of Windows. If you have the Excel or MS Office disk from the old machine you can use that to install the applications on the new PC. Of course, once you have it running you must uninstall the apps for the old PC. You can install Excel 97, Excel 2000, Excel 2002 or Excel 2003 on the new PC. If you do not have the disks then you are in for a shopping spree. Best wishes Berna...

CRM 3.0 and Web Site Alert email
I installed CRM 30 SBE on our SBS Premium box and it installed fine. It seems to be functional too, even the reports. But i have been getting an email warning: Web Site Alert from SBS 2003 stating: "http://SERVER:5555/MSCRMServices request failed: Critical condition. HTTP Status: 400 Bad Request Response Time (msec): 0. (WMI Status: 0 )" It sends me the email every 3 minutes. I also tried accessing this URL and i get: HTTP Error 403 - Forbidden Internet Explorer error message stating: You might not have permission to view this directory or page using the credentials you su...

help #12
i cannot access my emails ...

Chart #3
I'm trying to crate a chart where to information in the cells can be typed on a slant Hi Quiana, Having difficulting understanding your question. Maybe you could provide a little more detail. Quiana wrote: > I'm trying to crate a chart where to information in the cells can be typed on a slant -- Cheers Andy http://www.andypope.info To display a cell's text on a slant: 1. Select the cell 2. Choose Format>Cells 3. Select the Alignment tab 4. In the Orientation setting, drag the red diamond, or select a number of degrees 5. Click OK Quiana wrote: > I'm...

Excel charts won't appear or preview. Help!
Hello, In Excell 2003 I cannot get my charts or graphs to appear or preview. I was able too at first. But I accidentally hit right click and then clear several times after having created template line chart for the first time. This subsequently one at a time removed different aspects of my chart until my chart was gone. And now i cannot get the charts to appear again. Not even a preview. Thank you in advance foe your help and advice. This problim has been solved. Thank you. ...

Charting 3 columns help
I am working on a staffing chart with three columns: Staffing Time Incident 6 3 1234 15 9 1234 I would like a graph to show the "Time" on the left the "Incident" on the bottom and Staffing in the chart I have racked my brain and have not figured out how, to make the chart work that is :o) Do I need to re-arrange the columns? a different way? Any help would be greatly appreciated. Steve In your brief sample, Incident has no variation. How do you want staffing to appear in the cha...

Newly Created Function not Working #3
Thank you Frank, I used your suggestion but I still was unable to ge this to work. This is actually the method I had already used. Would i be easier to just save this as an addin and have him install the addi on his machine? Do you think it may work if we do this? Again thank yo for oyur help -- nuve ----------------------------------------------------------------------- nuver's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1003 View this thread: http://www.excelforum.com/showthread.php?threadid=26961 I'll just chime in and give my $0.02 for what it's...

CRM 3.0 Installation help...2 errors
Going through the system requirements part of the setup I get a Warning on the IIS part. It cant access the URL-- Error accessing URL http://IRONWOOD-CRM: The remote server returned an error: (500) Internal Server Error. The second error I get is : Setup failed to validate specified Reporting Services Report Server http://ironwood-crm/ReportServer. Error: Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. Chris Holub MCP CCNA 1. Check your IIS and default CRM virtual directory is there issue accecing, can you send me detail or s...

Help with what should be a simple formula..
Hi guys! I'm blanking out on how to write this simple formula, and honestly, not sure how to search the forum for help with this particular one. I'm trying to find profit margins basically. I want to break down profits into 1 Hr, 2 Hrs 8 hrs, etc.. for example, I the time it takes to produce item#1...say 10 Minutes.. In the 1 hr box want to figure out the profit based on 60 minutes of work.. My table looks like this.. A1=Item#1; B1=Cost to make Item; C1=Selling Price; D1=Time to make Item; E1=60 Minute profit; E2=120 Minute Profie.etc.tec.etc. A2=Item#2................................

Formula Help Please?
I'm trying to figure out the best formula to resolve the following: A B C Name Jan. Feb 1 Barb 8 3 2 Group 4 5 3 Ernest 6 7 4 Group 7 9 5 Heidi 9 4 6 Group 4 8 7 Total ____ ______ What we are trying to accomplish, is a formula that totals the values in column "B", however, those values which are "group" (B2, B4 & B6) should be multipl...

help with loading ACT into Outlook2000?
help lol, can't seem to do it any suggestions? thanks Try Act! support? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, anonymous@discussions.microsoft.com asked: | help lol, can't seem to do it any suggestions? thanks ...

Upper case #3
How can I change a whole sheet of text into upper case ? You could run a macro like the following to do that: Sub UpperCase() For Each oCell In ActiveSheet.UsedRange oCell.Formula = UCase(oCell.Formula) Next oCell End Sub NOTE: This preserves any formulae on the worksheet, whereas changing: oCell.Formula = UCase(oCell.Formula) to: oCell.Value = UCase(oCell.Value) would convert any formula to their results (in upper case) Cheers "caredesign@clara.net" <anonymous@discussions.microsoft.com> wrote in message news:0ca501c425e2$27cf9a50$a401280a@phx.gbl... > How...

help on composing an aging report for transactions
hello all, I am new to SSRS. I need some help on how to create an aging report in ssrs. This is what my report should look like: Month identify $ $closed $Recovered $Open 0-30day 31-60day Jan 2009 $$$ $$ $$$ $$$ $$$ $$$ I am having trouble on the days column. My user want these fields to contains the total $$ amt that was recovered in 0 - 30days and 31 - 60 days. how to do that? Please help.... Sherry From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-...

Tax on shipping #3
How can I have sales tax applied to shipping charges using the RMS shipping function? Will they apply the same as they do for items on the invoice? Thanks, Dan Rishworth Enduro Sport, Inc. Toronto, Canada -- Dan Rishworth Enduro Sport, Inc. Tel: 416-449-0432 x26 http://www.endurosport.com/ Dan, Not until 2.0 in Jan 07 -- = Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with...

error 0X80070057 #3
I cant setup this mail service program. error 0x80070057 -- Thank You It's a long shot but see if the resolution in this article helps: http://support.microsoft.com/default.aspx?scid=kb;en-us;326842 "onelot" <onelot@discussions.microsoft.com> wrote in message news:F031D2BA-12A9-40A9-AFB8-814CF03B967F@microsoft.com... >I cant setup this mail service program. error 0x80070057 ...

CRM/Help Desk Software
Does Microsoft have a CRM solution comparable to Remedy Help Desk or Avaya. If someone can assist it would be greatly appreciated. CB Hi Carleton, I'm not aware of a Microsoft helpdesk but if you are simply just looking for alternatives, we are using a product called ieSupportManager. If you do a search on Google you will find the website. A free trial version is available which is fully functional. We purchased two copies. We use one for helpdesk / issue tracking and we use the other copy for bug tracking and defect tracking ( although it is primarily a helpdesk product ). There i...

HELP!! Windows Explorer Hangs on Startup!
Hi all, I've had this Presario 2195US laptop for 4-5 years; running XP from SP1 to (now) SP3... no problems. 2-3 weeks ago, I tried to connect a 160gb USB-encased hard drive with no luck... proceeding along I pretty much hosed my windows. Anyway, I decided to reformat/install a copy of XP Pro w/SP3 (thought XP Home didn't like 160gb) and, after a few MS updates, every time I boot the system it shows the DESKTOP, the bottom left of the TaskBar and NOTHING on bottom-right. Cursor moves, I can Ctrl-Alt-Delete and, when I move cursor to TaskBar area... cursor turns in...

Need help setting up fields on a liquor store import file.
What is the best way to set up the fields on the excel sheet (import) for a liquor store. Should I put the size of the bottle with the extended description or should i put the size in the sub description? I also have sku's (item lookup codes) for each child item but do not have one for the parent (a case of the product). How should I handle this? Do I make up my own lookup codes or should i just not use the parent child relationship and store everything as a child? What benefits does each one have? I always include the size in the primary description field. I always keep the d...

networkdays #3
I want to use networkdays to put together a time line, such as using starting date then adding 6 days to get the next date and so on. Wha does the formula look like for this? I tried A1 + 6 and got a date tha included the weekend. Please help.:confused -- ANY ----------------------------------------------------------------------- ANYA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3409 View this thread: http://www.excelforum.com/showthread.php?threadid=53871 Are you thinking of =workday(a1,6) Remember that the analysis toolpak addin must be loaded (tools|A...

need help with tables so form okat- multiple equipment for 1 job
Here goes. I know this should be simple not sure why I can't see this right. Here is what I want to end up with not sure the best way to set up the tables to get what I want. and/or best way to create the form(from a query) or using form wizard pulling in tables that I need. I want to END up with a Form for all possible JOBS(cleaning jobs) with equipment needed and soaps needed for each job. There are many pieces of equipment for each job - mop, bucket, etc.(can't figure out how to get this in a form without listing with a comma) Have so far - don't thinks the tables a...

HELP
On 2/10/2010 several MS Updates came up and I installed them. After that I found out I could NOT disable my Network Connection (right-click Taskbar Tray icon, select Disable). I the a dialog (screenshot at link below)... "It is not possible to disable the connection at this time. This connection may be using one or more protocols that do not support Plug-and-Play, or it may have been initiated by another user or the system account." http://www.mediafire.com/file/mfzwvmhi0aj/NetworkConnections.jpg Note that BEFORE I installed the updates on the same day I COU...

Another time calculation issue, thanks for any help!
Hello All, I was hoping to do this without to much difficulty but not having much luck. I got the round down to the nearest 15 minutes. NOW I would like to round up to the nearest 15 minutes (ie. .25, .5, .75 ONLY), in fifteen minute intervals. Here's some of what I've tried so far... Round(1.2/0.6,0) answer: 2 would like 1.25 Int(1.9*8.0+0.6)/8.0 answer: 1.875 would like 2 -Int(-1.2)/60 answer 1.66 would like 1.25 I store the time in decimal format (I need that for another calculation which works perfectly). Any Ideas? Gina Whipp "I feel I have been denied critical...

Import New Reports in to CRM 3.0
I have created several reports in Visual Studio. Is there a way to 'import' them into CRM so that all users can use them? Also is there a way to have these reports run on a schedule? thanks! Christian ...

Help with a summary
Hi all - I hope I ask my question clearly.. I would like to make a summary of one spreadsheet on another in the same workbook. The problem is that I have weekly columns and I want the summary to reflect only the most current column, which is not an accumulated total of the previous weeks. Is there a way to: In Sheet1 ask Excel to "Show the furthest right column" from Sheet2? I tried to do a lookup function but it was getting far too messy and not really working the way that I had hoped Any help is greatly appreciated Thank you! Kristine the trick is to put in a number that is h...

email newsletter help publisher 2002
Hi, i have office xp 2002...anyways, I want to create a newsletter to send via email, preferable not as an attachment..how exactly do i do this..thanks, Kasey www.weaim2pleez.com Create it as a web page, copy and paste into the email and send as HTML. Many folks will not accept HTML emails because of security reasons. Use the PDF alternative. Free writers are around. http://www.primopdf.com/ -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "weaim2pleez.com" <weaim2pleez.com@discussions.microsoft.com> wrote in message ...