Gather values using a date range to sum up profits for a single month.

[Walntek]

I have been trying to figure out how to work this with sumif, sumifs and Vlookup. I have either received errors or a $0.00 value. What I am trying to do is create a function that searches sales that are within the date range mm/dd/yyyy of a single month and sum up the total profits made for that month. Here is what my table looks like:

B.........................O......................Total profits
9/15/13................$265.13.................September
9/16/13................$265.13.................October
9/17/13................$265.13.................November

To sum up, I want to add all of the numbers in column o into my profits table when it is in the same month.

[Ace_XL]

try pivot tables

http://www.cpearson.com/excel/pivots.htm

[hemesh]

Hello Walntek ! can you upload a sample work book with results expected(sensitive data removed)

[Walntek]

Thank you for the information Ace, after reading that page I'm wanting something that will automatically update as I enter in new sales.

Hemesh - I will work on getting a screenshot of the master in just a moment

[Tony Valko]

Are the dates all within the same year?

[Walntek]

here is the screenshot, I entered in some random numbers for reference. The date range got messed up due to zooming out for the screenshot, but I hope you get the idea from this. there is the table I want the summed numbers to be placed in the bottom right of the screenshot. the information I want it to sum in on column o

Attachment 265470

Tony - yes these are all within the same year. however I would hope that this could be extended to work on multiple years, if I have to manually change the year in the master every year that wouldn't be a issue either.

[Tony Valko]

When I click on your screencap I get this message:

vBulletin Message
Invalid Attachment specified. If you followed a valid link, please notify the administrator

[Tony Valko]

Is this what you're wanting to do:


Data Range

	A	B	C	D	E
1	Date	Sales	-----	Month	Total
2	1/25/2013	66		Jan 2013	66
3	2/11/2013	2		Feb 2013	62
4	2/15/2013	7		Mar 2013	190
5	2/23/2013	53		Apr 2013	127
6	3/22/2013	25		May 2013	117
7	3/27/2013	72		Jun 2013	0
8	3/27/2013	93		Jul 2013	0
9	4/16/2013	35		Aug 2013	49
10	4/17/2013	92		Sep 2013	89
11	5/11/2013	91		Oct 2013	117
12	5/16/2013	26		Nov 2013	38
13	8/28/2013	49		Dec 2013	158
14	9/9/2013	89
15	10/9/2013	13
16	10/18/2013	14
17	10/22/2013	90
18	11/14/2013	38
19	12/17/2013	64
20	12/23/2013	94

D2:D13 are 1st of the month dates formatted to display as mmm yyyy.

This formula entered in E2 and copied down:

=SUMPRODUCT(--(TEXT(A$2:A$20,"mmm yyyy")=TEXT(D2,"mmm yyyy")),B$2:B$20)