Calculating within Date Ranges
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
|