Summarising data from one worksheet to another

[cullism]

I have a workbook which includes on worksheet which is essentially a database and to which I add entries as necessary (see attachment Excel 1.xls). A second worksheet is a summary of the data from the first one showing total items sold and total gross sales for a particular month (Excel 2.xls). I want to enter formulas in Excel 2 which search the columns on the far right of Excel 1 and enter the total sales and gross sales price opposite the relevant month. I used to be able to do this before I retired in 2001 but I have had no luck with VLOOKUP, SUMI, etc. Can anyone help?

[Pete_UK]

Is there any particular reason why you have these as two separate workbooks (files), rather than both as sheets in the same workbook?

Pete

[cullism]

The attachments I provided are copies taken from the original workbook. In actuality they are both worksheets in the same workbook. I have now attached the original workbook.

[Pete_UK]

You can use this formula in cell B2 of the Month Sold sheet:

=SUMIFS(SOLD!S:S,SOLD!$R:$R,">="&$A2,SOLD!$R:$R,"<="&EOMONTH($A2,0))

This will give you the total count of sales for the month, and copy this into C2 to get the sales by price, then copy both down to the bottom of your list of dates.

Hope this helps.

Pete

[cullism]

Amazing! Never in a million years would I have come up with this solution. Thanks very much.

[Pete_UK]

You're welcome - glad it worked for you. The SUMIFS function was only introduced in XL2007, so you may not have come across it if you retired before then.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete