SUMIF or SUMPRODUCT to calculate monthly sales of various products

Sheet1
In column C I have dates formatted as day/month/year; for example
31/01/2008 (usually there are several entries per day).
In column D I have product names (two products)
In column E I have the value of the product sales on that date.

Sheet 2
Column A I have the date formatted as mmm-yy
In Column's B and C I would like the sum of the sales of that product
for the month.

For example Sheet2

A            B          C
Month     Shoes   Bags
Jan-07     $3250    $1800
Feb-07     $1250    $4210
etc

I tried formatting the date column in Sheet1 as mmm-yy and then using
a pivot table but the pivot table saw the dates in the original format
dd/mm/yyyy (that is, not as unique months).

SUMIF is able to sum the total value of each product sales but I can't
get it to take the month into consideration as well.
I suspect that I need to use SUMPRODUCT but I am not sure how.
At some point in the future I may include more products in my summary
too (which will mean extra column in Sheet2). Alternatively it might
be better to have my products in column A and the months in columns B,
C etc.

Can anyone suggest a solution please?

0
7/30/2008 12:42:25 PM
excel 39879 articles. 2 followers. Follow

5 Replies
755 Views

Similar Articles

[PageSpeed] 26

Put this in B2 of Sheet2:

=3DSUMPRODUCT((MONTH(Sheet1!$C$1:$C$1000)=3DMONTH($A2))*(YEAR(Sheet1!$C
$1:$C$1000)=3DYEAR($A2))*(Sheet1!$D$1:$D$1000=3DB$1),Sheet1!$E$1:$E$1000)

I've assumed you have up to 1000 rows of data on Sheet1 - adjust if
you have more. Format as you wish, then Copy into C2, then copy B2:C2
down as required. If you have another product, just put that in D1 and
copy the formula into D2 and down.

Hope this helps.

Pete

On Jul 30, 1:42=A0pm, Harry Flashman <flashdav...@gmail.com> wrote:
> Sheet1
> In column C I have dates formatted as day/month/year; for example
> 31/01/2008 (usually there are several entries per day).
> In column D I have product names (two products)
> In column E I have the value of the product sales on that date.
>
> Sheet 2
> Column A I have the date formatted as mmm-yy
> In Column's B and C I would like the sum of the sales of that product
> for the month.
>
> For example Sheet2
>
> A =A0 =A0 =A0 =A0 =A0 =A0B =A0 =A0 =A0 =A0 =A0C
> Month =A0 =A0 Shoes =A0 Bags
> Jan-07 =A0 =A0 $3250 =A0 =A0$1800
> Feb-07 =A0 =A0 $1250 =A0 =A0$4210
> etc
>
> I tried formatting the date column in Sheet1 as mmm-yy and then using
> a pivot table but the pivot table saw the dates in the original format
> dd/mm/yyyy (that is, not as unique months).
>
> SUMIF is able to sum the total value of each product sales but I can't
> get it to take the month into consideration as well.
> I suspect that I need to use SUMPRODUCT but I am not sure how.
> At some point in the future I may include more products in my summary
> too (which will mean extra column in Sheet2). Alternatively it might
> be better to have my products in column A and the months in columns B,
> C etc.
>
> Can anyone suggest a solution please?

0
pashurst (2576)
7/30/2008 1:18:17 PM
=SUMPRODUCT(--(MONTH(Sheet1!$C$2:$C$20)=MONTH(Sheet2!$A2)),
                          --(YEAR(Sheet1!$C$2:$C$20)=YEAR(Sheet2!$A2)),
                          --(Sheet1!$D$2:$D$20=B$1),Sheet1!$E$2:$E$20)

-- 
__________________________________
HTH

Bob

"Harry Flashman" <flashdavies@gmail.com> wrote in message 
news:6f712717-487d-4915-bcb7-a21b907664b7@z6g2000pre.googlegroups.com...
> Sheet1
> In column C I have dates formatted as day/month/year; for example
> 31/01/2008 (usually there are several entries per day).
> In column D I have product names (two products)
> In column E I have the value of the product sales on that date.
>
> Sheet 2
> Column A I have the date formatted as mmm-yy
> In Column's B and C I would like the sum of the sales of that product
> for the month.
>
> For example Sheet2
>
> A            B          C
> Month     Shoes   Bags
> Jan-07     $3250    $1800
> Feb-07     $1250    $4210
> etc
>
> I tried formatting the date column in Sheet1 as mmm-yy and then using
> a pivot table but the pivot table saw the dates in the original format
> dd/mm/yyyy (that is, not as unique months).
>
> SUMIF is able to sum the total value of each product sales but I can't
> get it to take the month into consideration as well.
> I suspect that I need to use SUMPRODUCT but I am not sure how.
> At some point in the future I may include more products in my summary
> too (which will mean extra column in Sheet2). Alternatively it might
> be better to have my products in column A and the months in columns B,
> C etc.
>
> Can anyone suggest a solution please?
> 


0
BobNGs (423)
7/30/2008 3:01:36 PM
On Jul 30, 11:18=A0pm, Pete_UK <pashu...@auditel.net> wrote:
> Put this in B2 of Sheet2:
>
> =3DSUMPRODUCT((MONTH(Sheet1!$C$1:$C$1000)=3DMONTH($A2))*(YEAR(Sheet1!$C
> $1:$C$1000)=3DYEAR($A2))*(Sheet1!$D$1:$D$1000=3DB$1),Sheet1!$E$1:$E$1000)
>
> I've assumed you have up to 1000 rows of data on Sheet1 - adjust if
> you have more. Format as you wish, then Copy into C2, then copy B2:C2
> down as required. If you have another product, just put that in D1 and
> copy the formula into D2 and down.
>
> Hope this helps.
>
> Pete
>
> On Jul 30, 1:42=A0pm, Harry Flashman <flashdav...@gmail.com> wrote:
>
>
>
> > Sheet1
> > In column C I have dates formatted as day/month/year; for example
> > 31/01/2008 (usually there are several entries per day).
> > In column D I have product names (two products)
> > In column E I have the value of the product sales on that date.
>
> > Sheet 2
> > Column A I have the date formatted as mmm-yy
> > In Column's B and C I would like the sum of the sales of that product
> > for the month.
>
> > For example Sheet2
>
> > A =A0 =A0 =A0 =A0 =A0 =A0B =A0 =A0 =A0 =A0 =A0C
> > Month =A0 =A0 Shoes =A0 Bags
> > Jan-07 =A0 =A0 $3250 =A0 =A0$1800
> > Feb-07 =A0 =A0 $1250 =A0 =A0$4210
> > etc
>
> > I tried formatting the date column in Sheet1 as mmm-yy and then using
> > a pivot table but the pivot table saw the dates in the original format
> > dd/mm/yyyy (that is, not as unique months).
>
> > SUMIF is able to sum the total value of each product sales but I can't
> > get it to take the month into consideration as well.
> > I suspect that I need to use SUMPRODUCT but I am not sure how.
> > At some point in the future I may include more products in my summary
> > too (which will mean extra column in Sheet2). Alternatively it might
> > be better to have my products in column A and the months in columns B,
> > C etc.
>
> > Can anyone suggest a solution please?- Hide quoted text -
>
> - Show quoted text -

Hi Pete, I think you have helped before - so once again, thank you.. I
couldn't get you formula to work at first so I alterted it slightly:
=3DSUMPRODUCT((MONTH(Sheet1!$C$2:$C$1000)=3DMONTH(Sheet2!
$A2))*(YEAR(Sheet1!$C$2:$C$1000)=3DYEAR(Sheet2!$A2))*(Sheet1!$D$2:$D
$1000=3DB$1),Sheet1!$E$2:$E$1000)

I changed the reference from C1 to C2 and then it worked fine.
I was expecting this to be an array formula because the last time I
used SUMPRODUCT for a similar request I think I had to. But not this
time. Interesting. I have much to learn.
0
7/31/2008 4:10:50 AM
On Jul 31, 1:01=A0am, "Bob Phillips" <Bob...@somewhere.com> wrote:
> =3DSUMPRODUCT(--(MONTH(Sheet1!$C$2:$C$20)=3DMONTH(Sheet2!$A2)),
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 --(YEAR(Sheet1!$C$2:$=
C$20)=3DYEAR(Sheet2!$A2)),
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 --(Sheet1!$D$2:$D$20=
=3DB$1),Sheet1!$E$2:$E$20)
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Harry Flashman" <flashdav...@gmail.com> wrote in message
>
> news:6f712717-487d-4915-bcb7-a21b907664b7@z6g2000pre.googlegroups.com...
>
>
>
> > Sheet1
> > In column C I have dates formatted as day/month/year; for example
> > 31/01/2008 (usually there are several entries per day).
> > In column D I have product names (two products)
> > In column E I have the value of the product sales on that date.
>
> > Sheet 2
> > Column A I have the date formatted as mmm-yy
> > In Column's B and C I would like the sum of the sales of that product
> > for the month.
>
> > For example Sheet2
>
> > A =A0 =A0 =A0 =A0 =A0 =A0B =A0 =A0 =A0 =A0 =A0C
> > Month =A0 =A0 Shoes =A0 Bags
> > Jan-07 =A0 =A0 $3250 =A0 =A0$1800
> > Feb-07 =A0 =A0 $1250 =A0 =A0$4210
> > etc
>
> > I tried formatting the date column in Sheet1 as mmm-yy and then using
> > a pivot table but the pivot table saw the dates in the original format
> > dd/mm/yyyy (that is, not as unique months).
>
> > SUMIF is able to sum the total value of each product sales but I can't
> > get it to take the month into consideration as well.
> > I suspect that I need to use SUMPRODUCT but I am not sure how.
> > At some point in the future I may include more products in my summary
> > too (which will mean extra column in Sheet2). Alternatively it might
> > be better to have my products in column A and the months in columns B,
> > C etc.
>
> > Can anyone suggest a solution please?- Hide quoted text -
>
> - Show quoted text -

Bob your formula also worked for me. Thank you. Your's differred
slightly from Petes though. You used "--" instead of *.
Also you used the "-- "symbols near the start of the formula.
What does "--" mean?
0
7/31/2008 4:12:28 AM
You're welcome - thanks for feeding back.

You can achieve the same thing using a SUM(IF( ... structure as an
array formula, and this would have to be committed using Ctrl-Shift-
Enter. SUMPRODUCT generally does not need to be.

Pete

On Jul 31, 5:10=A0am, Harry Flashman <flashdav...@gmail.com> wrote:
>
> Hi Pete, I think you have helped before - so once again, thank you.. I
> couldn't get you formula to work at first so I alterted it slightly:
> =3DSUMPRODUCT((MONTH(Sheet1!$C$2:$C$1000)=3DMONTH(Sheet2!
> $A2))*(YEAR(Sheet1!$C$2:$C$1000)=3DYEAR(Sheet2!$A2))*(Sheet1!$D$2:$D
> $1000=3DB$1),Sheet1!$E$2:$E$1000)
>
> I changed the reference from C1 to C2 and then it worked fine.
> I was expecting this to be an array formula because the last time I
> used SUMPRODUCT for a similar request I think I had to. But not this
> time. Interesting. I have much to learn
0
pashurst (2576)
7/31/2008 8:23:19 AM
Reply:

Similar Artilces:

Office 2003 Excel calculation is slow?
Using Win XP Pro and Office Professional 2003. When calculating a large macro driven linked workbook on the network server - the calculation is very slow. When I sign on with my roaming profile and calculate the same workbook - using Office Standard, the calculation is very fast? Any thoughts or recommendations? Anyone ?? "John Leonard - Sage" <sagegrp@adelphia.net> wrote in message news:uYVKBZEJHHA.1240@TK2MSFTNGP03.phx.gbl... > Using Win XP Pro and Office Professional 2003. > > When calculating a large macro driven linked workbook on the network > serv...

Month display in Microsoft Outlook 2003's Calendar
When using Microsoft Outlook 2003's Calendar in Month view, Saturday and Sunday are combined as one square (yes, they do have the date numbers and a line between them). This may be convenient for people using Outlook only for business purposes, but if someone were to have a job that involved weekends, or was not using Outlook for business purposes at all (which is my case), this could be extremely annoying. I would like to know if there is a way to display the Month view as the standard Sunday on the left and Saturday on the right, with Monday through Friday in between. I want all d...

Versions of Office products
Hi all, I am trying to find versions each of the products of Office for Mac. Like the versions of the following : Office 98 for Macintosh Microsoft Excel 98 for Macintosh Word 98 for Macintosh Microsoft PowerPoint 98 for Macintosh Microsoft Excel X for Macintosh Microsoft PowerPoint X for Macintosh Office X for Macintosh Microsoft Excel 2001 for Macintosh Office 2001 for Macintosh Word 2001 for Macintosh Word X for Macintosh The version would be found on Help--> About XXXXX. The version number could be: a.b.ccc(major.minor.build). I would not be able to install the products. So reques...

CRM 3.0 - Sales Pipeline
Hi, I've created a sales process (event Manual) in the workflow manager and i've created some oppotunities in which i've applied the sales process. When i run the Sales Pipeline report the name of the sales process doesn't appear in the Group by Sales Process field. Do you know if i have to do somethig else so i can select the sales process i've created in the Group by Sales Process field? Thanks Rute The sales process will only be picked up for the opportunities that selected in the report. Does your report have opportuntities on which you ran hte sales process? ...

Point of Sale 1.0 and RMS
Hey Guys, Can RMS connect to a POS 1.0 Database? I've got a client that is using POS 1.0, and wants to use RMS for the backend. Is that possible? Or do I have to convert him to RMS for both the POS and the backend? If so does a license for each PC have to be obtained or is the $2k+ price tag for RMS cover that. Thanks! Good qustions. Let see: 1- RMS and POS1.0 are not compatible. So you will have to install RMS on all machines 2- You should migrate your POS data to RMS (No RMS Tool for this). Contact me and I will send you that. 3- In US the price including BREP is about US$1,...

Error Deleting Product
When we try to delete this one product, it says that it cannot because it is associated with another record. Any ideas? The product you try to delete is being used in other related objects such as opportunity, quote, order, invoice... those records need to be remove prior to deleting the product. Good luck. Frank Lee Workopia, Inc. www.workopia.com "Chase D" wrote: > When we try to delete this one product, it says that it cannot because it is > associated with another record. Any ideas? ...

Calculation Problem 01-19-10
All though the formula is correct ( a simple math addition between 2 cells) ..There are a few of these cells in my 2003 XL Spread Sheet that consistently makes a 1 cent error in the addition - that is, it's adds the cell less 1 cent (formatted in currency) How do I fix this? Thanks http://www.mcgimpsey.com/excel/pennyoff.html -- Kind regards, Niek Otten Microsoft MVP - Excel "djm123" <djm123@discussions.microsoft.com> wrote in message news:6411803B-537F-43A2-921F-049D576FD19F@microsoft.com... > All though the formula is correct ( a simple math...

CRM Sales for Outlook
I have had to re-install Sales for Outlook a couple of time (including moving from one CRM target server to another, and back again), and now when I try to 'Go Off- line' I get an error stating 'The subscription already exists.'. Does anyone know how to resolve this? Thanks ...

the Problem about calendar show at the day/week/month model
1:I created an appoint. but at the day/week/month model, can not show the subject. if I change the model to"by category" , the subject show again. 2: the reminder can not work normaly at any model. any suggestion be appreciate. Richard rxj88@hotmail.com, you wrote on Thu, 15 Dec 2005 17:34:41 +0800: > 1:I created an appoint. but at the day/week/month model, can not show the > subject. if I change the model to"by category" , > the subject show again. Start Outlook once with the parameter /cleanviews Start | Run | outlook.exe /cleanviews > 2: ...

Calculate the amount of time it takes for a report to run
In SSRS2005, the was a way to calculate how long a report took to run. I got the formula here: (http://www.ssw.com.au/Ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx#UsefulFooter). The formula works flaulessly in 2005, however in 2008 the formula changes its behavior characteristics because of the way the rendering engine was changed in 2008. In 2005, when the report finished the calculation showed you how long it took for the report to run. In 2008, it does the same thing, but then it gets all messed up when you interact with the report by things like hitting ...

math calculation
I have never used Excel before. Is it possible to assign a value to a column so that it performs a mathematical calculation on another column? For instance: I would like column G to be triple the value of column A. Thanks q LOL - The amazement you'll have as you learn what it really is capable of. In G1 put =A1*3 If you have a range of values, eg A1:A20, then put that formula in cell G1, then copy cell G1, select the range G2:G20 and paste and it will automatically fill in the correct formulas for you. Excel is capable of extremely complex math calcs, formulas, logic problems...

Opportunities & Opportunity Products 12-02-05
Two Questions: Is there a way in CRM to copy an Opportunity from one Account to another? If not, is there a third-party add-on that provides this ability? Is there a way in CRM to select and add multiple products to an Opportunity instead of selecting them from the price list one at a time? If not, is there a third-party add-on that provides this ability? While these features do not exist there are many ISV's on this group who could write them for you at a price.. Its all down to how much these features are worth to you ========================= John O'Donnell Microsoft CRM...

sending e-mails in sales process to a DL group in Outlook
E-mails need to be sent to more than one credit analyst in the opportunity sales process workflow when an activity on an opportunity is closed. The e-mail activity follows the wait on the activity to close. E-mail in workflow manager for an opportunity can be sent to a logical user (owner, owners manager, account,contact, account/contact), hard coded user, contact or account. How do I get an e-mail to a user group in workflow? We have identified options below do you have anything better? Create a DL Group for the Credit Analysts then 1. Setup a Contact as a group for the Credit Analysts. Creat...

How do I use a check box to accept a calculation
I am trying to create a ledger that has both current balance and the balance my bank has. Right now it can only tell me what my current balance is. My question is, How can I use a check box to initiate a calculation? This way is there is no check in the box then the calculation does not happen, but when I put a check in the box the calculation happens. This way when my bank shows something that has posted I can place the check and automatically balance my check book. Could you specify exactly, what your ledger format is? "Joejoethecrackman" wrote: > I am trying to cre...

Product Lookup 06-11-04
I understand you can add or import products into the catalog etc, but I want the products to be retrieved real time from another database. How do I achieve this? Neil ...

How do I calculate the z factor automatically
How do I calculate the z factor automatically by using Hall-Yarborogh equation without changing (Y) every time to reach F=0 ? if any body know .please answer me. INPUT DATA: T 68 oF p 1000 psia SGFG 0.5 air=1 CALCULATE CRITICAL AND REDUCED TEMPERATURE AND PRESSURE: Tpc=169.0+314.0*SGFG Ppc=708.75-57.5*SGFG Tpr=(T+460.0)/Tpc t=1/Tpr Ppr=p/Ppc CALCULATE TEMPERATURE DEPENDENT TERMS: A=0.06125*t*EXP(-1.2*(1.-t**2) B=t*(14.76-9.76*t+4.58*t*t) C=t*(90.7-242.2*t+42.4*t*t) D=2.18+2.82*t CALCULATE REDUCED DENSITY Y Y = ASSUMED 0.324006985 F=-A*Ppr+(...

product / currency / pricelist
Hi, I search in the documentation and in the web but could not find enough information to really be sure :-) I thought it must be possible with CRM 4.0 to have: one product and many currencies and one discount list for each currency (in %) and one pricelist for each currency Please give me some info why I can't do it and what approach would be the best to manage the following solution: (or what I do wrong that I am not allowed to discount a product that comes with the main currency and should be attached in the pricelist for currency b) the product is "software" - the price ...

Uninstalling ALL Office products
We had 2003 and 2000 running on one computer fine. Then, one day last month 2003 stopped working properly. So, we uninstalled it, and then 2000 stopped working properly. We can not find the disks for the 2003 programs, but I have the disks for 2000. I need to totally uninstall ALL Office products and updates, and start over again. How do I uninstall Word, Excel, and PowerPoint? I don't want to lose Outlook. See the reply to your previous post of the same question. Posting the same message every 10 minutes does not make responses any quicker or any better. Be patien...

Conditional Calculation
The conditional statement I'm working with is: =IF(AND(A3=A2,B3=B2), 'Au'!E1,'Au'!E2) Here's the problem, I want to fill the column with this conditional statement but if I do that it will automatically convert E1 to E2 then E3 ect like it was filling a series. So instead of getting 5 rows with E2 then swithcing to E3 for 6 rows, I get E1, E2, E3. I want the value to be E1 until AND(A3=A2,B3=B2)is false and then I want to go to the next value (E3). Again, filling E3 in until AND(Ax=Ax,Bx=Bx) is false and then switching to E4 ect. Is there a way to write...

Joining 2 SUMIF's ???
How can I join the following SUMIF's in to a single formula? Please help my heads about to blow up :) =SUMIF(Country range, country criteria,revenue range) =SUMIF(Opportuntity range, Opp criterion, revenue range) Need to apply both criterion to the SUM revenue range. How do I do this?? =sumproduct(--(countryrng=countrycriteria),--(opprng=oppcriteria),revrng) =sumproduct() likes to work with numbers. the -- stuff converts trues and falses to 1/0's. AC-H wrote: > > How can I join the following SUMIF's in to a single formula? Please help my > heads about to blow ...

sales transaction entry 12-17-09
When a sales transaction occurs what tables are affected? The main tables are SOP10100 and SOP10200 for Open Invoice / Orders, and SOP30200 and SOP30300 for History, there are other within the range for Comments, Serial / Lot Numberes if you need that detail. Mick "Larry C" wrote: > When a sales transaction occurs what tables are affected? Hi Larry, You can install the SDK from CD2 to provide you with greater details. Saved SY00500 – Batch Header SOP10100 – SOP_HDR_WORK SOP10200 – SOP_LINE_WORK Other Tables SOP10101 SOP_Commission_WORK_HIST SOP10102 ...

Calculation of days
In column A are a series of consecutive dates, as an example A1=1/15/04, A2=1/26/04, A3=2/3/04, etc. In Column B I want to show the number of week days between dates minus the week-end dates. Again, using my initial example, in column B1 I am intending to show the number of days between A1 and A2, which in this case B2=7 (11 total days minus 4 weekend days). Any thoughts on the correct formula to accomplish this would be most appreciated. Use the NETWORKDAYS function. It also accommodates a list of holidays. You have to install the Analysis Tool Pak to use it. See Help for the func...

Sample Java Sales process needed
Hello, We are looking to implement a sales process without any activities. Mainly because we want to know what stage an opportunity is in, but we do not need the activities automatically created. Therefore, I think we should be able to get there by using the salesstagecode drop-down field. Although I read in this forum that sample Java sales processes are available, I can never find them. Does anyone have any suggestions/sample Java sales processes? That would be really helpful. Thanks Michiel here is an example for a 3 stage process that updates the close probability var i = 0; switch (...

Sumif??? #3
Ken, It is still including events that have no point balance, THANKS though -- KellyMcG0 ----------------------------------------------------------------------- KellyMcG03's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1454 View this thread: http://www.excelforum.com/showthread.php?threadid=26169 ...

Quote Product Price Per Unit
Hello, I have an issue I cannot resolve : When I add a product to my quote product and save, the Price per Unit shows up equal to 0.00. However the price in my product contains a value. It means the mapping is not done beetween the product and the quote product (The user has then to overwrite the price with the value) What it strange is that this is happening only for a few products, the others are working fine ... Any Idea ? Thanks, Check out the Product and Price List setting. Since this is affecting only some product, I would compare these setting with the products that work ...