SUMIF formula's for months

[mikeywhits]

Hi all,

I am trying to do an sumif formula based on the following...

I am entering a number of dates into cells A1 - A30 using the format 01/01/11, 06/07/11 etc..

What i want to then do is an IF formula which will search column A1-A30 using the criteria of numerical month for example "01", then adding together all numbers from cells B1-B30 based on this criteria.

Is this possible to do? thanks.

 

[VoG]

Try

=SUMPRODUCT(--MONTH(A1:A30)=1),B1:B30)

 

[T. Valko]

Hi all,

I am trying to do an sumif formula based on the following...

I am entering a number of dates into cells A1 - A30 using the format 01/01/11, 06/07/11 etc..

What i want to then do is an IF formula which will search column A1-A30 using the criteria of numerical month for example "01", then adding together all numbers from cells B1-B30 based on this criteria.

Is this possible to do? thanks.

Try this...

=SUMPRODUCT(--(MONTH(A1:A30)=1),B1:B30)

When you enter the month number DON'T use the leading 0.

Note that if there are any empty cells within the data range of A1:A30 they will evaluate as month number 1.

In that case then use this version:

=SUMPRODUCT(--(A1:A30<>""),--(MONTH(A1:A30)=1),B1:B30)

 

[Weaver]

try:

=SUMPRODUCT(--(MONTH(A1:A30)=1),B1:B30)

 

[mikeywhits]

Thanks guys,

Both worked, but i did have empty fields for dates so perfect now.

 

[T. Valko]

Thanks guys,

Both worked, but i did have empty fields for dates so perfect now.

You're welcome. Thanks for the feedback!

 

[mikeywhits]

Hi Guys,

just realised i need to do this over the period of 2 years so can the above formula be refined to search for the month 01 and year 2011. thanks again.

 

[VoG]

Try

=SUMPRODUCT(--(A1:A30<>""),--(MONTH(A1:A30)=1),--(YEAR(A1:A30)=2011),B1:B30)

 

[mikeywhits]

Thanks VoG, works perfect

 

[T. Valko]

Hi Guys,

just realised i need to do this over the period of 2 years so can the above formula be refined to search for the month 01 and year 2011. thanks again.

If you're also testing for a specific year then the test for empty cells is not needed (unless you're testing for January 1900).

=SUMPRODUCT(--(MONTH(A1:A30)=1),--(YEAR(A1:A30)=2011),B1:B30)

=SUMPRODUCT(--(TEXT(A1:A30,"myyyy")="12011"),B1:B30)