Sumifs Sate Formula

**nic84** · 07-22-2011, 06:31 AM

aaaaarrrrrrrgggggghhhhhh I hate date Forumla's!!!!!!!

Workings Sheet:
Column A - Provider
B1 - June 2011

Data Sheet:
Column A - Duration
Column B - Provider
Column C- Date

What i would like to do in cell B2 in the workings sheet, is to sum based on 2 criterias:

Criteria one, sum by provider
Criteria two, then by the month stated in cell b1

The date format in the data sheet is DD/MM/YYYY and can be any day of the month.

the forumla i have sort of come to is (not actual formula copied from excel so there may be a few ' and ! missing)
=Sumifs('data sheet' A:A,'data sheet'B:B,'working sheet'A2,'data sheet'C:C,month('working sheet'B1))

Yeah i could just insert a new column in the data sheet with the formula
=Date(year(C1),month(C1),day(1)

But i would like to keep data to a minimum and also maybe one day understand date formula.

Any help appreciated and thanks in advace

**arthurbr** · 07-22-2011, 07:13 AM

aaaaarrrrrrrgggggghhhhhh I hate having to type data when I don't have to

Please provide a sample worksheet

**Azam Ali** · 07-22-2011, 07:55 AM

hI nic84

try it

=SUM(IF('data sheet'!B:B&MONTH('data sheet'!C:C)='working sheet'!$A2&MONTH('working sheet'!$B$1),'data sheet'!A:A))

Kindly confirm it throuh Ctrl+Shift+Enter as it is an array formula