Sum a range based on another column with dates where the MONTH matches a drop down list in another cell?

[NamssoB]

I have a list of stock trades, all dated in column O (from O5:O5000). I have a profit/loss in column U starting at U5. I have a drop down in U1 to select Month (3-letter abbreviation), as well as the word "Total". My goal is to be able to select Jan from the drop down, and have it show me my profit from January. Change to Feb, and I only see profit from Feb. Change to Total, and it shows me total profit.

I want to select Jan, Feb, Mar, etc., in the drop down, and have cell U2 show me a total for all items with a date within the month selected in the drop down. For example:

U1: Drop down, with a list (Total, Jan, Feb, Mar, ...)
U2: Formula I'm trying to figure out: SUM U5:U5000 for any entries with a date (Column O5:O5000, 12-Feb, 5-Jan, etc) that matches the month selected in U1
U5... Values I want to sum
O5... Dates

I know I can use TEXT(O5,"mmm") to grab the 3-letter month and compare it to U1, but I can't figure out how to get that into my formula in U2.

And when I select Total in U2, I just want to add everything up, the entire column U starting at U5.

Ideas?

 

[tyija1995]

Hey,

When you write months such as "Jan", "Feb", "Mar" etc you can access them as a value if you use =MONTH("1/"&$U$1) (where $U$1 is the month in this example, so if it takes "Feb" as the value this formula will return 2) - this can then be compared to the month of the dates in cell O and used as criteria for your formula.

Because you also have to consider the word "Total" you will want to nest this into an IF condition, and check for this word first as it won't like =MONTH("1/"&"Total") of course.

If cell U1 is empty it will return an error, which you could nest an IFERROR into, or give yourself a prompt that cell U1 should be populated, personal preference I suppose!

Try this in cell U2:

IF($U$1="Total",SUM(U5:U5000),SUMPRODUCT((MONTH("1/"&$U$1)=MONTH(O5:O5000))*(U5:U5000)))

 

[NamssoB]

Thanks, but with that formula, I get #Value! for anything other than Total in U1.

I tried modifying it to this, and for #SPILL! error:
IF($U$1="Total",SUM(U5:U5000),IF(MONTH("1/"&$U$1)=MONTH(O5:O5000), SUM(U5:U5000),))

 

[Peter_SSs]

Try this. BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

NamssoB.xlsm
	O	P	Q	R	S	T	U
1							Aug
2							173.00
3
4
5	14-Dec						13.00
6	02-Nov						109.00
7	21-Oct						(40.00)
8	20-Oct						(38.00)
9	18-Oct						45.00
10	06-Sep						(41.00)
11	23-Aug						(32.00)
12	22-Aug						121.00
13	02-Aug						84.00
14	20-Jul						130.00
15	20-Jul						4.00
16	18-Jul						32.00
17	14-Jul						117.00
18	27-Jun						(7.00)
19	23-Jun						(44.00)
20	17-Jun						73.00
21	06-Jun						46.00
22	24-May						103.00
23	17-May						0.00
24	14-May						58.00
25	07-May						4.00
26	25-Apr						(10.00)
27	15-Apr						10.00
28	14-Apr						(26.00)
29	27-Mar						82.00
30	16-Mar						(57.00)
31	01-Mar						107.00
32	22-Feb						(42.00)
33	16-Feb						37.00
34	16-Feb						124.00
35	14-Feb						(47.00)
36	12-Feb						103.00
37	03-Feb						(6.00)
38	26-Jan						117.00
39	23-Jan						53.00
40	13-Jan						3.00
Sheet1

Cell Formulas
Range	Formula
U2	U2	=SUM(FILTER(U5:U5000,IF(U1="Total",O5:O5000>0,TEXT(O5:O5000,"mmm")=U1),0))

 

[NamssoB]

Solved! Your formula worked perfectly, thank you! And I also changed my profile, thanks for the tip!

The good news is that I can now take this and apply it to other columns (total # of trades, average trade size, gain/loss per trade, etc.) I'll also modify the ranges to use OFFSET because I use macros/VBA buttons to add new rows. THANK YOU!

 

[NamssoB]

Final formula I used, including the offsets:

=SUM(FILTER(OFFSET(U2,3,0):OFFSET(U2,3000,0),IF(U1="Total",OFFSET(O2,3,0):OFFSET(O2,3000,0)>0,TEXT(OFFSET(O2,3,0):OFFSET(O2,3000,0),"mmm")=U1),0))

 

[NamssoB]

Ok - new problem with this that I just discovered VERY STRANGE!

- The Worksheet is sorted descending by close date (column O). So blank / unclosed positions are at the top, Feb is next, Jan below that, etc. Once we hit march, then it will be
Blank / Unclosed
Mar
Feb
Jan

If I select Total, it works (it only sums closed positions)
If I select Feb, it works (it only sums Feb positions)
If I select Jan, it FAILS. It sums Jan + the blank/Unclosed positions at the top!

I added some March positions to see if it's "positional", and it appears to NOT be positional and occurs with JAN only. In other words, when I add Mar dates, I get the following:

Total - sums total only
Mar - sums Mar only
Feb - sums Feb only
Jan - sums Jan + BLANKS at top

Then I thought it must be because Jan is at the bottom, so I resorted so Jan is at the top....SAME PROBLEM! For some reason, it's including values entered into any blank unclosed position with the Jan SUM.

Here's the formula I'm using currently.
=SUM(FILTER(OFFSET(U2,3,0):OFFSET(U2,3000,0),IF(U1="Total",OFFSET(O2,3,0):OFFSET(O2,3000,0)>0,TEXT(OFFSET(O2,3,0):OFFSET(O2,3000,0),"mmm")=U1),0))

Even with your original formula without the Offsets, it behaves the same:
=SUM(FILTER(U5:U5000,IF(U1="Total",O5:O5000>0,TEXT(O5:O5000,"mmm")=U1),0))

Disclaimer: For this exact use case, this error isn't a problem because Blank/Unclosed positions will always be $0, hence the sum won't be affected. However, I discovered this error when trying to re-use this formula in other columns that actually have values in the blank fields, and found that they're skewing the data

Some photos to see what it's doing:

$809.52 is correct for JAN, shown below.

See how adding $1000 to an unclosed positions gets added to JAN numbers:

Correct Total:

Total still correct with value in blank position:

The same occurs with Mar and Feb. The error seems to occur only with Jan dates.

 

[Peter_SSs]

Solved! Your formula worked perfectly,

Since more than one person suggested a formula, it is a good idea to ensure readers know who you are referring to (eg by quoting a small part like I have above).

I'll also modify the ranges to use OFFSET because I use macros/VBA

Since OFFSET is a volatile function I would avoid that if possible, & it is possible in this case - see my suggestion below.

If I select Jan, it FAILS. It sums Jan + the blank/Unclosed positions at the top!

I think that I have corrected that below.

Try this in U2 instead.

=SUM(FILTER(U5:INDEX(U:U,5000),IF(U1="Total",O5:INDEX(O:O,5000)<>"",(TEXT(O5:INDEX(O:O,5000),"mmm")=U1)*(O5:INDEX(O:O,5000)<>"")),0))

 

[NamssoB]

Since more than one person suggested a formula, it is a good idea to ensure readers know who you are referring to (eg by quoting a small part like I have above).

Since OFFSET is a volatile function I would avoid that if possible, & it is possible in this case - see my suggestion below.

I think that I have corrected that below.

Try this in U2 instead.

=SUM(FILTER(U5:INDEX(U:U,5000),IF(U1="Total",O5:INDEX(O:O,5000)<>"",(TEXT(O5:INDEX(O:O,5000),"mmm")=U1)*(O5:INDEX(O:O,5000)<>"")),0))

Awesome, this worked better - thank you!

The reason I was using OFFSET is because I use a button to add a new record to the top, so formulas in the top row that started out as U5:U5000 change to U6:U5001. Using OFFSET eliminates this. Your new formula about solves the blank record problem perfectly, but not the offset issue. I can easily address this in my VBA code by pasting your formula back into the Cell.Value property when I click on my Add new Record button, but...is there a way to prevent the offset problem without needing to do this, while still enjoying the benefit of your latest formula?

 

[Peter_SSs]

The reason I was using OFFSET is because I use a button to add a new record to the top, so formulas in the top row that started out as U5:U5000 change to U6:U5001. .....Your new formula about solves the blank record problem perfectly, but not the offset issue.

Just change all the references to row 5 in my formula to row 4 and you still shouldn't need OFFSET.

=SUM(FILTER(U4:INDEX(U:U,5000),IF(U1="Total",O4:INDEX(O:O,5000)<>"",(TEXT(O4:INDEX(O:O,5000),"mmm")=U1)*(O4:INDEX(O:O,5000)<>"")),0))