Chris,
Here's your sample, with the solution implemented in a new sheet: x
http://freefilehosting.net/download/445hk
sumproduct bucketing counts by dates age.xls
With C18:F18 containing: 180, 360, 720, 720 (last one is just a text label)
In C20:
=SUMPRODUCT(($C$2:$C$15=$B20)*(TODAY()-$D$2:$D$15<=C$18)*($D$2:$D$15<""))
In D20:
=SUMPRODUCT(($C$2:$C$15=$B20)*(TODAY()-$D$2:$D$15C$18)*(TODAY()-$D$2:$D$15<=D$18))
Copy D20 to E20
In F20:
=SUMPRODUCT(($C$2:$C$15=$B20)*(TODAY()-$D$2:$D$15E$18)*($D$2:$D$15<""))
Copy C20:F20 down to F27
The additional checks for the lower/upper limits C20 and F20,
ie this term: ($D$2:$D$15<"")
is to exclude the any blanks in col D (dates) from calculations,
preventing wrong results
(Blank cells are evaluated as zeros by Excel)
The check above effectively replaces the need to check col C < col B, since
dates will only be input in col D where col C < col B, going by my
observations
I've also amended your right/bottom SUM formulas to cover the correct ranges
The above set-up should work fine for you
Pl press the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Chris waller" wrote:
Max,
I have uploaded a new file for you to have a look at, it might make it a bit
clearer and all the columns are in the correct place. I will try to explane
it a bit clearer. Where column C is different to column B and there is a date
in column D these and only these records I am interested in. In the summary
table B19:G28 I have created a row for each temporary grade. I now need to do
a couple of calculations using the date in column D and comparing it with the
current date shown in column I. The figure in column J is the difference
divided by 30 to give the average month. There does not appear to be any
correlation between the formula and the grades that are being looked at. When
I change the date in column D to make the time period shorter the figures in
the summary table do not alter to reflect this.
http://freefilehosting.net/download/445fj