Sum, If, Dates

**table83** · 10-20-2009, 01:25 PM

I've been struggling with this problem for 3 days now and I'm desperate. I know it's something simple that I just don't know yet. Hope someone here knows how to do this stuff.

I have a sales sheet for one of my classes that I'm trying to add a sum if statement too. The dilema is that there are 3 criteria that must be met for for it to sum what I want. I want it to differentiate according to the product (T-shirt), type of purchase (credit), and by the date.

I got it all down except for the date.
This is what I have so far:

=SUM(IF(DATEVALUE(10/12/2009)<A:A<DATEVALUE(10/16/2009),IF(F:F=("T-Shirt),IF(C:C=("credit"),D:D))))

I want the values in row D to be added together if row A is inbetween date 10/12/2009-10/16/2009 and the value of row C is "credit", and the value of row F is "T-shirt". If any value in the criterion is not met then I want it to disavow the colum. If you delete the date part it works just fine.

I've attached the file. What am I doing wrong?
Thanks ahead of time.

**JBeaucaire** · 10-20-2009, 01:34 PM

Add the dates to your sheet so it's easy to use this in H8:

=SUMPRODUCT(--($A$5:$A$16>=$I$4), --($A$5:$A$16<=$I$5), --($C$5:$C$16=$G8), --($F$5:$F$16=H$7), $D$5:$D$16)

**shg** · 10-20-2009, 01:39 PM

This really begs for a pivot table, though. Add a column that calculates week number.

**table83** · 10-20-2009, 01:42 PM

Originally Posted by JBeaucaire

Add the dates to your sheet so it's easy to use this in H8:

=SUMPRODUCT(--($A$5:$A$16>=$I$4), --($A$5:$A$16<=$I$5), --($C$5:$C$16=$G8), --($F$5:$F$16=H$7), $D$5:$D$16)

May I ask how this is different than the sum ifs? Also are the -- needed? What do they do?

Thanks for the super fast response.

**table83** · 10-20-2009, 01:45 PM

Originally Posted by shg

This really begs for a pivot table, though. Add a column that calculates week number.

I've heard wonderful things about pivot tables, and I even tried with this. Unsuccesfully. I'm good at excell, but there are so many things that I really need to learn.
Thanks

**DonkeyOte** · 10-20-2009, 02:07 PM

Given you're using XL2007 you should use SUMIFS in preference to SUMPRODUCT - see XL Help for more info.

**table83** · 10-24-2009, 05:11 PM

OK, New data is put in and the formula is missing one of the cells. Whats going on? The highlighted cell (D25) isn't added into the sum of the formulas like the other ones are. What gives?