# Sumifs - incorrect when using date in critera

K

#### Kathy

I have a file that includes several sheets. On the first sheet, I am trying
to pull in total requirement quantities for part numbers based on the date
which are located on another sheet.

This is the formula that I am using
=SUMIFS('Material needed for open orders'!\$D\$3:\$D\$401,'Material needed for
open orders'!\$A\$3:\$A\$401,'Raw Material Planning'!\$B2,'Material needed for
open orders'!\$C\$3:\$C\$401,">'Material needed for open orders'!\$O\$1",'Material
needed for open orders'!\$C\$3:\$C\$401,"<='Material needed for open
orders'!\$P\$1")

My first sheet looks like this

"Value
Stream" PART # Material KanBan Inv. Level Pcs OH Mat Req Wk R

Slice Rings 20330 10018 66000 24600 41400 207.0 0.0
20756 10214 45000 0 45000 43.2 0.0
20843 10043 50000 14701 35299 15.2 0.0

My second sheet is this

(a) (c) (d) (e) (f)
(g)
LPROD Sch Sh Dt LQORD LQSHP LCLAS Remaining Qty
1-1924 01/30/09 10000 0 JA 10000
1-40 12/04/08 5113 0 JA 5113
1-40 12/04/08 104000 0 JA 104000
1-40 12/17/08 104000 0 JA 104000
1-40 12/17/08 104000 0 JA 104000
9-40 11/10/08 100 42 JG 58

Columns O & P referenced in the formula are date fields to show weekly
buckets. I can get the formula to work properly without using the dates, but
when I add the date criteria, all of my totals are 0.

Your formula is incorrect and SUMIFS is correct, when using a criteria like
a cell you need to use the ampersand

,">"&O1

not

,">O1"

where it will look for the literal string O1

the ampersand tells it to look for the contents of the cell

--

Regards,

Peo Sjoblom

Thank you for your quick response. Where do I put the ampersand when I am
referencing a cell on a different sheet?

Kathy

Peo,

Your solution works, thank you so much!!!!

Peo Sjoblom said:
Your formula is incorrect and SUMIFS is correct, when using a criteria like
a cell you need to use the ampersand

,">"&O1

not

,">O1"

where it will look for the literal string O1

the ampersand tells it to look for the contents of the cell

--

Regards,

Peo Sjoblom