How do I ignore day and only refer to the the month?

**wickerman** · 03-09-2009, 08:37 AM

The attachment illustrates my problem.

The summary page summarises the source data into how much revenue each department has made over 12 months.

However the formula in Summary worksheet column B will only total up the amounts if the month in the Source worksheet column B is the 1st of the month. How do I change the formula in Summary worksheet column B to purely go by month and ignore the day?

Hope that makes sense. I've been fiddling with this for a while and need a break!

**NBVC** · 03-09-2009, 08:41 AM

Try:

=SUMPRODUCT(--(Source!$A2:$A14=B$1),--(MONTH(Source!$B2:$B14)=MONTH($A2)),Source!$C2:$C14)

**arthurbr** · 03-09-2009, 08:57 AM

Does attached file help ?
Its is made on the assumption that month and year must be the same

BTW a Pivot Table would also give you these results

**wickerman** · 03-09-2009, 10:15 AM

Thanks NBVC & Athurbr:

Thanks for your suggestions. Arthubr answer works the best as my actual worksheet goes to over 400 rows and starts in the year 2000 so I need a formula that counts all the sums in a particular month and year. Didn't know I should have said that until I tried arthurbrs formula so thanks! You have saved many hours frustration!