Sumproduct/Countif

[Chris waller]

I have an Excel Spreadsheet and I am trying to summerise some of the data in
a table. I don't know which formula to use and I would not know how to go
about creating such a formula. I only need to look at the rows where column A
is different to column B. I need to use a date that appears in column D and
to calculate between that date and the current date whatever that may be and
put the count/sum of the occurences in the correct cell on the summary table
as below.

Business Area 0-6 months 6-12 months 12-24 months 24+months Total
UG7 TDA UG6 1 1
SEO TDA UG7
HEO TDA SEO
EO TDA HEO 2 2
AO TDA EO 1 1
AA TDA AO 1 1
Total 1 1 3 1 5

 

[Max]

This should get the core desired Aging buckets-counts going for you

Assume source data in Sheet1,
col A = Business areas, eg: xx, yy, etc
col D = Dates (these are presumed real dates)

In your summary sheet,
In A2 down are listed the unique business areas, eg: xx, yy, etc

Enter the nums: 180, 360, 720 into B11
Enter a label into E1: >720
B1:E1 will be the equivalent col headers for your:
0-6 months, 6-12 months, 12-24 months, 24+months
(assume 1 month = 30 days)

Place
In B2:
=SUMPRODUCT((Sheet1!$A$2:$A$10=$A2)*(TODAY()-Sheet1!$D$2:$D$10<=B$1))

In C2
=SUMPRODUCT((Sheet1!$A$2:$A$10=$A2)*(TODAY()-Sheet1!$D$2:$D$10>B$1)*(TODAY()-Sheet1!$D$2:$D$10<=C$1))
Copy C2 to D2

In E2:
=SUMPRODUCT((Sheet1!$A$2:$A$10=$A2)*(TODAY()-Sheet1!$D$2:$D$10>D$1))

Select B2:E2, copy down. Adapt the ranges to suit your actuals.
Put in the right/bottom row/col totals as desired.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Chris waller]

FAO: Max, thanks for your response, but unfortunately it does not work the
way I expected. A2 down which you refer to as unique business areas are not
exactly that. There can be multiple occurrences of the business areas within
the list. What I am trying to do is count those areas, but to put them into
some kind of timeframe, hence the summary table. For example, if there are
two business areas and they have a date in each which is about a month apart,
these need to be counted but as the first goes over the six month period that
should be recorded under the 6-12 months column whilst the other should be
shown under the 0-6 months column. I hope this makes it clearer?

 

[Max]

But I didn't presume the biz areas in the source Sheet1 were unique. They
could be multiple occurences, with different corresponding dates.

For easy ref, here's my test file based on my 1st response:
http://freefilehosting.net/download/44393
sumproduct bucketing by age.xls

Still think that my earlier interp & bucketing suggestion (with the
underlying assumption of 1 month = 30 days) was not incorrect. If your actual
scenario is different, upload your sample (you can use the same free filehost
link) then throw the link to it in reply here.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Chris waller]

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

 

[Max]

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$15>C$18)*(TODAY()-$D$2:$D$15<=D$18))
Copy D20 to E20

In F20:
=SUMPRODUCT(($C$2:$C$15=$B20)*(TODAY()-$D$2:$D$15>E$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]

Max,

Sorry I have taken so long to get back to you but I’m a bit under the
weather and have not turned my PC on for the past couple of days. You’re a
star. I input your formula on a spreadsheet and input some actual dates and
it has done exactly what it should. It will take the tedium out of one aspect
of the job and there is even chance it will take less time in the future to
come up with the correct figures. All I can say is thanks. It doesn’t seem
much but I would reciprocate the pleasure, but it’s obvious that you know a
lot more than I do anyway.

 

[Max]

Welcome, Chris. Please mark responses which help by clicking the YES buttons
(like the one below, from where you're reading this). Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik