Formula for adding numbers based on month

Hello. I am looking for a formula for N3 that adds up numbers in column J based of the date (Month only) in column A. The number 4 currently shown in N4 I have manually added, but I am looking for a formula that will give the same result.


For example:
test.jpg

I have also attached the test excel file.

N3:

Formula:

Please Login or Register  to view this content.

However, your dates do not appear to be true dates.


Regards, TMS

try this, copied down...
=SUMPRODUCT(--(TEXT($A$3:$A$26,"mmmm")=M3)*$J$3:$J$26)

Trevor beat me to it.
They seem like dates to me, Trevor, tested with =ISNUMBER(). I just opened the attached csv

@wonderd:

Will them not being true dates affect the result?

I would expect so.

@Ford:

They seem like dates to me

maybe it's just a case of Regional Settings. When I open the csv file, some dates are not recognised as dates, and those that are probably have the day and month the wrong way round. But, in your part of the world, you probably both see them the same way ... no problem.

1. if you opened Trevor's file, then you dont have 2003, Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version.

2. If your CSV always comes in that format, there should not ne a problem.

3. Both the formulas are pretty much identcical, Trev used , I used *

@ Trevor, yes that could be it. Mercans use mm/dd/yy (Im a Brit from Luton, via South Africa and USA)

remove the "sum" range...
=SUMPRODUCT(--(TEXT($A$3:$A$26,"mmmm")=M3))

Note this (and the others) will count/sum ALL Jan's and Febs etc. So if you had 3 years of data, it would count/sum data from all 3 Jans.

If this could be a problem, then you would need to include a year in M3 etc, and change the TEXT part of formula to this...
--(TEXT($A$3:$A$26,"mmmm yyyyy")=M3)

This could have the problem of converting M3 to a real date is you add 2015 to January - unless you change the format of the month cells to Text

Try:

Formula:

Please Login or Register  to view this content.

Regards, TMS

Try this modification...
SUM=SUMPRODUCT(--(TEXT($A$3:$A$26,"mmmm")=M3)*($A$3:$A$26<>"")*$J$3:$J$26)
COUNT=SUMPRODUCT(--(TEXT($A$3:$A$26,"mmmm")=M3)*($A$3:$A$26<>""))
They both worked on your sample data....

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	1/25/2015		TRUE							2			January	4	2
4	2/25/2015		FALSE							20			February	22	2
5	3/1/2015		FALSE							2			March	4	2
6	4/15/2015		FALSE							2			April	4	2
7	5/13/2015		FALSE							2			May	4	2
8	6/10/2015		FALSE							2			June	4	2
9	7/16/2015		FALSE							2			July	4	2
10	8/5/2015		FALSE							2			August	4	2
11	9/16/2015		FALSE							2			September	4	2
12	10/1/2015		FALSE							2			October	4	2
13	11/1/2015		FALSE							2			November	4	2
14	12/1/2015		FALSE							2			December	4	2
15	1/25/2015		TRUE							2
16	2/25/2015		FALSE							2

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3			TRUE							2			January	2	1
4	2/25/2015		FALSE							20			February	22	2
5	3/1/2015		FALSE							2			March	4	2
6	4/15/2015		FALSE							2			April	4	2
7	5/13/2015		FALSE							2			May	4	2
8	6/10/2015		FALSE							2			June	4	2
9	7/16/2015		FALSE							2			July	4	2
10	8/5/2015		FALSE							2			August	4	2
11	9/16/2015		FALSE							2			September	4	2
12	10/1/2015		FALSE							2			October	4	2
13	11/1/2015		FALSE							2			November	4	2
14	12/1/2015		FALSE							2			December	4	2
15	1/25/2015		TRUE							2

N3=SUMPRODUCT(--(TEXT($A$3:$A$26,"mmmm")=M3)*($A$3:$A$26<>"")*$J$3:$J$26)
O3=SUMPRODUCT(--(TEXT($A$3:$A$26,"mmmm")=M3)*($A$3:$A$26<>""))

GHreat

Did you read my note in post #9 regarding the year?

OK good