Simple SUMIFS problem

[henryg]

I have data in a worksheet (this is simplified) such as

Date (dd/mm/yy)	Val
17/08/22	148
18/08/22	145
1/09/22	151

and I want to sum the Val column by month without using a pivot table, so I try eg

=SUMIFS(b2:b4,a2:s4,MONTH(a2:a4)=8)

and it evaluates to zero; actually it spills zeros (I'm using 365) - I will want to spill the result by month but as that didn't work I tried what I though would be a simple version using a specific month (8). And as it spills I thought I'd sum the values as test a even though summing zeros gives a predictable zero.

I know I'm doing something stupid, but I just can't figure out what.

 

[Fluff]

How about

=SUMIFS(b2:b4,a2:a4,">="&DATE(2023,8,1),a2:a4,"<"&DATE(2023,9,1))

 

[Phuoc]

Or try

=SUMPRODUCT(B2:B4*(MONTH(A2:A4)=8))

 

[henryg]

I prefer the simplicity of the sumproduct solution, but thank you to both. Any pointers on where/why my SUMIFS fails?

 

[Fluff]

Your formula is using an array of TRUE/FALSE as the criteria for the dates in col A so will always return 0

 

[henryg]

Ah, the light begins to dawn; even though by evaluating the formula I saw the array I didn't think it through. Hence the SUMPRODUCT. I see now. Thanks.

 

[Fluff]

Glad we could help & thanks for the feedback.