Data analysis of dates, splits & segments of dept codes

I'm trying to analyse data and summarise it by date (greater than 60 days, and between 30 days and 60 days), by department using the first two digits of department codes, and where there are multi-line order numbers...

Apart from that, it's laughs all the way. A sample of the data is attached.

Thansk for any ideas...

First you need to alter J2 to be 25- (double listing 24 presently)

Second, to avoid horrendous formulae I would advise creating a concatenation key in O, such that:

O12: =LEFT(K12,2)&"#"&L12
copied down for all rows

Then for results of unique Inv numbers...

E3:
=SUMPRODUCT(--(LEFT($K$12:$K$300,LEN(E$2))=E$2),--($M$12:$M$300<=$D3),1/COUNTIF($O$12:$O$300,$O$12:$O$300&""))
copied across to L3

E4:
=SUMPRODUCT(--(LEFT($K$12:$K$300,LEN(E$2))=E$2),--($M$12:$M$300>=$C4),--($M$12:$M$300<=$D4),1/COUNTIF($O$12:$O$300,$O$12:$O$300&""))
copied across to L4

For the summation copy E3:E4 to E6:E7 but alter the final argument of the SUMPRODUCT from: 1/COUNTIF(...) to simply $I$12:$I$300

You may want to think about using Dynamic Named Ranges in the Long Term.

Originally Posted by BB

Regarding the LEFT, why do you use LEN() as opposed to just "3"

Flexible... if you had 99-, 100- in consecutive cells you would need to alter the formula if the value was hardwired, by using LEN it will auto adjust.

Originally Posted by BB

how does the 1/Countif work

Consider:

A1:A3
a
b
a

Count of unique items is obviously 2 - a & b but how to calculate...

Well given my total output for all "a" should = 1 I need to apportion 1 accordingly... so let's say in B I do the following:

B1: =1/COUNTIF($A$1:$A$3,$A1)
and copy down to B4

The results I generate are thus:

B1: --> 1/2 --> 0.5
B2: --> 1/1 --> 1
B3: --> 1/2 --> 0.5

The SUM of B1:B3 is thus 2.

I can achieve the same result without use of B using a SUMPRODUCT to iterate the calcs

C1: =SUMPRODUCT(1/COUNTIF(A1:A3,A1:A3))

so the above essentially conducts three 1/COUNTIF calcs (ie for A1, A2, A3 being the criteria) and sums the resulting output.

the use of &"" etc as used in the real example is a way to handle blanks.

I should point out however that the method provided is flawed should an invoice be split over different thresholds (30, 60 days etc) but on the same distribution code... this doesn't appear to be an issue in your sample file.