Calculating sum based on month and year

[Newguy25]

Hi, I've gotten good help here in the past, I'm hoping someone can help with a formula I just can't seem to get working.

I have a sheet with all sorts of sales figures. I can calculate the totals of any column that has a date in the 'won' field. What I'm hoping to do is calculate the values 'won' per month/year. I can do this currently overall, but haven't been able to sort how to do this when also incorporating the year.

The first field where there should be a value is W22, my formula I'm currently workshopping is below.

=SUMPRODUCT(($J$2:$J$800="WON")*(TEXT($I$2:$I$800,"MMM")=V22)--(TEXT($I$2:$I$800,"YYYY")=P2)*($G$2:$G$800))

Once I have that field reliably working, I can carry that to other months/years.

Attached is a sample sheet. My excel version is 2016. Thanks in advance for any suggestions you can provide.

[alansidman]

Using Power Query in the attached is the full solution, but I am only showing two years of summary in the thread.

Excel 2016 (Windows) 64 bit

	A	B	C
1	Status	Month:Year	Totals
2	INACTIVE	May:2017	49367
3	WON	May:2017	43345
4	INACTIVE	June:2017	69089
5	WON	June:2017	19387
6	WON	July:2017	6215
7	LOST	July:2017	6591
8	WON	August:2017	15506
9	LOST	August:2017	70000
10	INACTIVE	October:2017	81213
11	WON	October:2017	19650
12	INACTIVE	November:2017	21800
13	INACTIVE	December:2017	60975
14	WON	December:2017	36190
15	INACTIVE	January:2018	49955
16	LOST	January:2018	37740
17	WON	January:2018	6750
18	INACTIVE	February:2018	33358
19	WON	February:2018	22908
20	INACTIVE	March:2018	50000
21	WON	March:2018	14535
22	WON	April:2018	16349
23	LOST	April:2018	37768
24	INACTIVE	May:2018	20784
25	INACTIVE	June:2018	31750
26	WON	June:2018	9261
27	INACTIVE	July:2018	21850
28	WON	July:2018	18126
29	WON	August:2018	48550
30	INACTIVE	August:2018	36778
31	WON	September:2018	110879
32	INACTIVE	September:2018	109580
33	INACTIVE	October:2018	118458
34	WON	October:2018	137143
35	LOST	October:2018	41000
36	INACTIVE	November:2018	25250
37	WON	November:2018	16631
38	INACTIVE	December:2018	1900

Sheet: Table1

Here is the Mcode

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[Pete_UK]

You could use this formula in cell W18:

=SUMPRODUCT(($J$2:$J$800="WON")*(TEXT($I$2:$I$800,"MMM")=V18)*(YEAR($I$2:$I$800)=P$2),$G$2:$G$800)

then copy it down to W29. Note that for other years you will need to change the P$2 parameter shown in red.

Note also that by arranging it like this you have all the conditions before a comma, then the sum range after.

Hope this helps.

Pete

[Newguy25]

Thank you Pete. The formula still doesn't seem to work for me. The first month that I had any sales was May 2017 (W22), using the formula suggested in that cell still turns up a value of zero.

Attachment 885921

[alansidman]

Have you looked at the Power Query solution? It does start in May 2017. If you only want the "Won" then you can filter out the other categories.

[Newguy25]

I've dabbled in power query, but some of the other things in there weren't working. I still think it's where this sheet is heading, but hoping to have it all working crudely first then evolve from there.

[Pete_UK]

See the attached file, where I have put the formula in column W for 2017 and 2018 (shown in blue).

Hope this helps.

Pete

[alansidman]

but some of the other things in there weren't working.

Care to elaborate on this?

[Newguy25]

@Pete_UK - Terrific, I can't thank you enough!

@alansidman - I went through the conversion process and several formulas that were working stopped. The sample i've submitted is only a shell of the entire sheet. I'm sure it's due to my unfamiliarity with power query. Since I had so much working already, I was hesitant to carry on until I had a fully working sheet. It is planned to evolve to PQ when possible because I'm aware of its advantages.

[Pete_UK]

Glad to help, and thanks for marking the thread as Solved.

You might also 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