Calculating within Date Ranges

[MB]

I've gone through the threads and haven't found a solution to my problem.

For simplicity's sake, let's say I have 4 columns

Category, Start Date, End Date, Total Number of Days.

On a monthly basis, I need to calculate the number of days completed for
each of 8 categories. The date range spans months.

If I were to write it out, the querry would be
if <category> in column A = "x", count number of days up to and including
Aug 31st."

Any help is most appreciated...

MB

 

[Sean Timmons]

If you wanted just total per category, it would be:

Will number of days be filled out for each row? end date - start date..

If so, then for the entire table, =sumif(A:A,"Category Name",D)

For date ranges, =sumproduct(--($A$2:$A$10000="Category
Name"),--($B$2$B$10000>=Desired start date)"),--($C$2$C$10000<=Desired end
date),--($D$2:$D$10000)) would give number of days within a range.

 

[MB]

Thanks for the help... I tried that and I keep getting an error... here's my
formula:
"Summary!C2" is the cell with the start date.
"Summary!D2-1" is the start date of the next month minus 1 day

any suggestions... it doesn't like the third array...

=SUMPRODUCT(--(data!$C$36:$C$1000="u1"),--(data!$E$36:$E$1000>=Summary!C2))--(data!$F$36:$F$1000<=(Summary!D2-1))--(data!$H$36:$H$1000)

 

[Sean Timmons]

Not sure if you had a typo or not, but should be

=SUMPRODUCT(--(data!$C$36:$C$1000="u1"),--(data!$E$36:$E$1000>=Summary!C2),--(data!$F$36:$F$1000<=(Summary!D2-1)),--(data!$H$36:$H$1000))

Should work as is..