Unable to use a column with dates as a condition for a sumif function

Hi,

I tried to use Conditional Sum Wizard to create a formula that I can use and drag down for an entire column. I would like one of the conditions to equal the date in each row so for each cell in the column there should be different value because each coincides to the specific date in the row. I can put in the specific date using the Conditional Wizard, but when I change it to the cell where the date is it will not accept. it. Below is the formula the Conditional Wizard gave me..

=SUM(IF('Time Detail'!$D$2:$D$300="26-7624",IF('Time Detail'!$A$2:$A$300=DATEVALUE("6/5/2013"),'Time Detail'!$B$2:$B$300,0),0))

I tried to put an actual cell for the datevalue criteria, however this did not work. This is the formula I tried to use.

=SUM(IF('Time Detail'!$D$2:$D$300="26-7624",IF('Time Detail'!$A$2:$A$300=A9,'Time Detail'!$B$2:$B$300,0),0))

Additionally, if I copy the first formula and change the date it still doesn't work. Very confused...

Can anyone help me out with this?

Thanks,
Elise

After you type the formula (or make changes), press CTRL + SHIFT + ENTER..
Not just Enter

Also, you can use SUMIFS instead of the more complex array formula

=SUMIFS('Time Detail'!$B$2:$B$300,'Time Detail'!$D$2:$D$300,"26-7624",'Time Detail'!$A$2:$A$300,A9)

Hope that helps.

TimeDetail.xlsx That's what I thought.. and no the date is formatted as date. Here is the file. I put the formula in sheet TIME, and the cell is K8.

This works for me
=SUM(IF('time detail'!$E$3:$E$22="26-7624",IF('time detail'!$B$3:$B$22=A9,'time detail'!$C$3:$C$22,0),0))
with 6/4/2013 in A9.

IMPORTANT
You must enter the formula with CTRL + SHIFT + ENTER
This is what makes the {brackets} appear around the formula
Do not enter those brackets by hand.

Also this non array formula
=SUMIFS('time detail'!$C$2:$C$300,'time detail'!$E$2:$E$300,"26-7624",'time detail'!$B$2:$B$300,A9)

I tried this formula for cell k8 and I get a value of zero. The value should be 51

Did you put the date 6/4/2013 in A9 ?

Here's your file with the new formula(s) in it.
Your original formula still in K8

A9: 6/4/2013

L8: =SUM(IF('time detail'!$E$3:$E$22="26-7624",IF('time detail'!$B$3:$B$22=A9,'time detail'!$C$3:$C$22,0),0))
Entered with CTRL + SHIFT + ENTER

M8: =SUMIFS('time detail'!$C$2:$C$300,'time detail'!$E$2:$E$300,"26-7624",'time detail'!$B$2:$B$300,A9)

EFewool.xlsx

Oh, got it! Thank you for your help! Much appreciated!