[FONT="Arial"]Hello Peeps, [/FONT]
[FONT="Arial"]I am tryingto use the sumproduct function within Excel 2010 to carry out a sumproductwhich are within a certain month. [/FONT]
[FONT="Arial"]At the moment,I have these three headings[/FONT]
[FONT="Arial"]Column A:List of dates spanning two years form of “DD/MM/YY”[/FONT]
[FONT="Arial"]Column B:weights[/FONT]
[FONT="Arial"]Column C:metal concentration. [/FONT]
[FONT="Arial"]What I have beentrying to do is do a sum product of the weights and metal concentration duringa specific month. Any ideas? [/FONT]
[FONT="Arial"]Thanks inadvance, Matt [/FONT]
Using the Sumproduct function within a certain date range



Re: Using the Sumproduct function within a certain date range
Try:
=SUMPRODUCT((TEXT($A$2:$A$100,"mm/yy")="01/12"),$B$2:$B$100)
where 01/12 means January 2012. You can substitute for a cell reference. If the cell contains a date, then use the TEXT function to extract month/year...
adjust ranges to suit. This sums the Weights. Similar formula for concentration, subbing C2:C100 for B2:B100. 
Re: Using the Sumproduct function within a certain date range
Hi geobear
You could also SUMIFS as you have Excel 2010. 
Re: Using the Sumproduct function within a certain date range
Yes you can, you need to check for all values greater than or equal to first of the month, and at the same time less than or equal to the end of the month.
e.g
=SUMIFS($B$2:$B$100,$A$2:$A$100,">="&EOMONTH(X1,1)+1,$A$2:$A$10,"<="&EOMONTH(X1,0))
where X1 contains a date you want to use to determine the month of interest.
or other ways depending on your setup.

Re: Using the Sumproduct function within a certain date range
Hi Guys,
Thanks for the replys. I have done the SUMIFs anyway for the weights. However, I need to perform an actual sum product. So (weight1 * concentration1) + (weight2 * concentration2)..etc within a certain month. Thats what im failing to figure out.
Cheers,
Matt


Re: Using the Sumproduct function within a certain date range
Hey Guys,
I have found the solution using INDIRECT and ADDRESS, as shown below:
Code=SUMPRODUCT((INDIRECT("'MineActual'!"&ADDRESS(MATCH(B2,'MineActual'!A:A,0),3,1,1)&":"&ADDRESS(MATCH(B3,'MineActual'!A:A,0),3,1,1))),(INDIRECT("'MineActual'!"&ADDRESS(MATCH(B2,'MineActual'!A:A,0),4,1,1)&":"&ADDRESS(MATCH(B3,'MineActual'!A:A,0),4,1,1))))
Works perfectly..

Re: Using the Sumproduct function within a certain date range
you just need to add the other column:
=SUMPRODUCT((TEXT($A$2:$A$100,"mm/yy")="01/12"),$B$2:$B$100,$C$2:$C$100)

I have an interesting problem I cant figure out! I have a file which might make it easier but I will try to explain.
I have 4 worksheets in one workbook. Datasheet, Homeloans, YR2, Table.
On Datasheet in M2 I have a Date formatted as dmmmyy
On Homeloans I have Dates from A1:L1 which are derived from the date on Datasheet =DATE(YEAR(Datasheet!$M$2),MONTH(Datasheet!$M$2)+0,1) that progress up to =DATE(YEAR(Datasheet!$M$2),MONTH(Datasheet!$M$2)+11,1) in L1
In A2:L2 I have values enteredOn YR2 I have the same setup. The date system is setup to look like a fiscal year instead of a calendar year.
On the Table sheet from B1:Y1 I have dates. B1 uses the formula =IF(Datasheet!M2<>"",TEXT(Datasheet!M2,"dmmmyy"),"Jan") B2 uses =IF(OR(B$1="",B$1=""),"",TEXT(EDATE(B$1,1),"dmmmyy")) and is propagated to Y1.
In B2:Y2 I am using this formula =SUMPRODUCT((TEXT(Homeloans!$A$1:$L$1,"dmmmyy")=B$1),Homeloans!$A$2:$L$2) with it changing respectively to Y2
This is the tricky part.
On Table, Cell C18 I have a data validation list setup to select a year number
On Table, E18 I have formula =SUMPRODUCT((RIGHT(TEXT($B$1:$Y$1,"dmmmyy"),1)+0=$C$18),$B$2:$Y$2)
Here is the problem.
If I set the date on Datasheet to 1/11/2011 and I select year one on C18 on the Table sheet, I get the correct sum on E18.
If I change the date on Datasheet to 1/11/2012 it flips the value in E18 to zero. This is the weird part. If I change the value in C18 to 2 for second year, then it gives me the correct sum. So I played with the formula
=SUMPRODUCT((RIGHT(TEXT($B$1:$Y$1,"dmmmyy"),1)+0=$C$18),$B$2:$Y$2) and found that if I change the highlighted part to +1 then select year one again, it gives me the correct sum.It sort of does what I need it to do but the problem is for every date set I will have to go in and change that value up and down the number line according to the date on Datasheet and that just isnt going to work. It need to be dynamic and pull values based on the year the values show up in.
So if a payment is entered for example on 1/11/11 as the first payment and the second payment on 1/12/11, and I want to know the totals for the first year of the loan, it should only give me that value in E18.
Any help in resolving this would be greatly appreciated. Im running out of forehead to bang against the wall!
Regards,
D 
Welcome to the forum!
Please start your own thread with this request. The thread you have ppsted to is very old  if you think it's relevant, then post a link to it in your new thread.
Thanks.

Welcome to the forum! Please start your own thread with this request. The thread you have ppsted to is very old  if you think it's relevant, then post a link to it in your new thread. Thanks.


Welcome to the forum! Please start your own thread with this request. The thread you have ppsted to is very old  if you think it's relevant, then post a link to it in your new thread. Thanks.

Welcome to the forum! Please start your own thread with this request. The thread you have posted to is very old  if you think it's relevant, then post a link to it in your new thread. Thanks.

Ok, thank you.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!