SUMIFS Calculation

[nelsonsix]

Hi All,

Hopefully you can help with my problem.

I am trying to calculate the number of points in one column based on the users name and the date by month. Example data is:

Column A Column B Column C
Name Date Points
Nick 20/01/2018 5
Will 01/01/2018 2
Nick 04/02/2018 3


So I want to calculate how many point Nick has for January, i can get it to work based on any other criteria apart from specifying the month of January. My formula that isn't working is:

=SUMIFS(C:C,A:A,"Nick",B:B,"**/01/2018")

No matter what wildcard variation I try I cant seem to get a correct answer

Is there a solution to this?

Thanks for your help.
Neil

 

[Marcelo Branco]

Welcome to Mr Excel Forum

Try
=SUMIFS(C:C,A:A,"Nick",B:B,">="&DATE(2018,1,1),B:B,"<="&EOMONTH(DATE(2018,1,1),0))

Hope this helps.

M.

 

[AlKey]

Try this
=SUMPRODUCT((MONTH(B$1:B$3=1)*(A$1:A$3="Nick"))*C$1:C$3)

 

[nelsonsix]

Ah perfect Marcelo, this worked a treat, I would have never got that! Thanks you for your quick reply.

Neil

 

[Marcelo Branco]

Neil,

You are welcome. Thanks for the feedback.

M.