SUMIFS with Dates
 

I have a conditional SUMIFS

Detail Sheet

Type Date Qty
Credit 2/1/10 1
Sales 2/2/10 2
Credit 2/15/10 3

Summary Sheet

Week # Start Date End Date Credits Sales
1 2/1/10 2/5/10 1 2
2 2/8/10 2/15/10 3 0

So, basicallay I am summing the Credit column if the Detail is Type Credit
and the Date is = the Start Date on the line and the End Date <= the Date on
the line. Same for Sales. However, as simple as it seems, it just seems not
lo like the dates.

=SUMIFS(Detail!C:C,Detail!A:A,"=B6",Detail!A:A,"< =C6",Detail!B:B,"Credit")

This keeps summing to 0. When I try testing things other than dates, it
works just fine. I tried sticking a '&' before the B6 and C6 - that did not
work.

Any ideas?

SUMIFS with Dates
 

Detail Sheet
Type Date Qty
Credit 2/1/10 1
Sales 2/2/10 2
Credit 2/15/10 3

=SUMIFS(Detail!C:C,Detail!A:A,"=B6",Detail!A:A," <=C6",Detail!B:B,"Credit")
I tried sticking a '&' before the B6 and C6 - that did not work.

Well, according to your Detail sheet the dates are in column B but your
formula is checking column A for the dates!

On your Summary sheet change the header Credits to Credit:

Then enter this formula in Summary D6:

=SUMIFS(Detail!$C:$C,Detail!$B:$B,"="&$B6,Detail! $B:$B,"<="&$C6,Detail!$A:$A,D$5)

Copy acroos to E6 then down as needed.

--
Biff
Microsoft Excel MVP


"d" wrote in message
...
I have a conditional SUMIFS

Detail Sheet

Type Date Qty
Credit 2/1/10 1
Sales 2/2/10 2
Credit 2/15/10 3

Summary Sheet

Week # Start Date End Date Credits Sales
1 2/1/10 2/5/10 1 2
2 2/8/10 2/15/10 3 0

So, basicallay I am summing the Credit column if the Detail is Type Credit
and the Date is = the Start Date on the line and the End Date <= the Date
on
the line. Same for Sales. However, as simple as it seems, it just seems
not
lo like the dates.

=SUMIFS(Detail!C:C,Detail!A:A,"=B6",Detail!A:A,"< =C6",Detail!B:B,"Credit")

This keeps summing to 0. When I try testing things other than dates, it
works just fine. I tried sticking a '&' before the B6 and C6 - that did
not
work.

Any ideas?