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

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: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" wrote:

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

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" wrote:

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: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" wrote:

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

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..

"MB" wrote:

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" wrote:

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: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" wrote:

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