Sumif question

[bionicle]

Hi All,

have a question for you, if I have a list of dates in the format of 05/01/2016 but want to return a sum value against all the dates listed as January how do I do it?

 

[bionicle]

=SUMPRODUCT((MONTH(A1:A100)=1)*((Q1:Q100)="WON")*B1:B100)

tried this and all I get is #VALUE! back?

 

[bionicle]

worked out what the issue is, I have a few empty cells in my date column and a few question marks etc, the formula works fine when the data is all there..

 

[Fishboy]

=SUMPRODUCT((MONTH(A1:A100)=1)*((Q1:Q100)="WON")*B1:B100)

tried this and all I get is #VALUE! back?

Hmmm, this is what I see in my test document (I'm only using 10 rows instead of 100):

Excel 2010
	A	B	C	D	E
1	01/01/2016	10	WON	=SUMPRODUCT((MONTH(A1:A10)=1)*((C1:C10)="WON")*B1:B10) --->	40
2	02/01/2016	10
3	03/01/2016	10
4	04/01/2015	10	WON
5	05/01/2016	10
6	06/01/2016	10
7	07/01/2014	10	WON
8	08/01/2016	10
9	09/01/2016	10
10	10/01/2016	10	WON
Sheet1

 

[Fishboy]

worked out what the issue is, I have a few empty cells in my date column and a few question marks etc, the formula works fine when the data is all there..

Yay! Glad to hear you got it sorted mate

 

[Aladin Akyurek]

worked out what the issue is, I have a few empty cells in my date column and a few question marks etc, the formula works fine when the data is all there..

=SUMPRODUCT(B1:B100,--(TEXT(A1:A100),"mmm")=R1),--(Q1:Q100="WON"))

where R1 = jan.