Sumprodcut formula to return data based on certain date ranges

[cartica]

Hi-

Please see my attached spreadsheet. The "turnover tab" is pulling in info from the "Trade Blotter Tab" I have just provided a small sample of data. I am trying to sum up the total USD from column "T" in the Trade blotter tab given the specific criteria in my formula in the turnover tab. The formulas can be viewed in the Buy and Sell column. II am trying to pull this data for each month. I am not sure what to add to my formula to only sum up usd for dates 1/1/12-1/30/12 etc for each month. The month formula won't work because I will also have 1/1/13-1/30/13. The trade blotter is coming from an outside system, so i believe dates are string. Please let me know your thoughts.

THanks

[TMS]

I can't look at it in detail at the moment but you'll need something like:

=SUMPRODUCT(--(MONTH(Sheet2!$A$2:$A$1000)=MONTH($A2)),--(YEAR(Sheet2!$A$2:$A$1000)=YEAR($A2)),--(other criterion 1),--(other criterion 2))

Where A2 contains a date in the month/year you are interested in, for example, 1 Jan 2013.


Regards, TMS

[TMS]

Just noticed the comment about dates being text. Select each column of dates and use Data | Text To Columns, delimited, no delimiters required, and then choose Date format and MDY as the format. That will convert to real dates. Do that for each column individually.


Regards, TMS

[cartica]

Thanks, it worked. Can you explain to me what -- does? I see you use a comma -- instead of the *(

[TMS]

You're welcome.

Personal choice; the -- is a double negative, that is, it is equivalent to multiplying by -1 twice ... so, a multiplication operation that coerces the results into numeric values. It has the same effect as using the multiplier (*) and there have been various debates about which is most efficient. I'm not sure if a conclusion has ever been reached. When I first was introduced to SUMPRODUCT, many years ago, I seem to recall the first example used -- and I sort of grew up with that. It also makes a more obvious break in the formula so it's easier to see the components ... in my opinion anyway.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.