Will DSUM function work here OR........

[S. Obaid]

A sheet having 5000 record of 150 customers for
accounts receivable. There are Three columns namely

SHEET - 1
=========
Customer Receivable($) Inv.Date(dd/mm) Aging
Company-A 600 05/07 2
Company-B 350 02/07 5
Company-C 500 25/06 13
Company-A 200 22/06 15
Company-C 100 11/06 26
Company-A 400 01/06 36
Company-A 300 05/05 63
Company-A 300 04/05 64

Now on a separate Sheet I need the following information from
the above sheet
SHEET - 2
========
CUSTOMER 1-30 Days 30-45 Days 46-60 Days
Company-A 800 400 600
Company-B 350 0 0
Company-C 600 0 0


Simply:
I want to retreive Total amount for specific period / Age.
Any idea would be greatly appreciated.....

Syed

 

[Max]

One way ..

Assuming the sample table as posted in Sheet1
is in cols A to D, data from row2 down

In Sheet2
-------------
With the list of companies in A2 down

Put

In B2:

=SUMPRODUCT((Sheet1!$A$2:$A$9=$A2)*(Sheet1!$D$2:$D$9>0),Sheet1!$B$2:$B$9)-SU
MPRODUCT((Sheet1!$A$2:$A$9=$A2)*(Sheet1!$D$2:$D$9>30),Sheet1!$B$2:$B$9)

In C2

=SUMPRODUCT((Sheet1!$A$2:$A$9=$A2)*(Sheet1!$D$2:$D$9>=30),Sheet1!$B$2:$B$9)-
SUMPRODUCT((Sheet1!$A$2:$A$9=$A2)*(Sheet1!$D$2:$D$9>45),Sheet1!$B$2:$B$9)

In D2:

=SUMPRODUCT((Sheet1!$A$2:$A$9=$A2)*(Sheet1!$D$2:$D$9>45),Sheet1!$B$2:$B$9)-S
UMPRODUCT((Sheet1!$A$2:$A$9=$A2)*(Sheet1!$D$2:$D$9>60),Sheet1!$B$2:$B$9)

Select B22 and fill down

Adjust the ranges to suit

Note that entire column refs (e.g.: A:A, B:B, etc)
cannot be used in SUMPRODUCT
and the ranges $A$2:$A$9, $B$2:$B$9, $D$2:$D$9
must be identical in structure

 

[Peo Sjoblom]

You can use advanced filter and sumproduct, first v=create a unique company
list, start from Sheet2 by selecting
the cell where you want the company header (let's assume Sheet2!A1), then do
data>filter>advanced filter,
select the company range (assume it is A1:A9 using your example), check
unique records only and copy to another location (select Sheet2!A1), click
OK. That should give you the company list used in your example,
then create the headers for the ageing, 0-30, 31-45 and so on, then use a
formula in (once again assuming)
B2 put

=SUMPRODUCT(--(Sheet1!$D$2:$D$9<=30),--(Sheet1!$A$2:$A$9=$A2),Sheet1!$B$2:$B
$9)

copy down for all 0-30 days

now in C2 put

=SUMPRODUCT(--(Sheet1!$D$2:$D$9>30),--(Sheet1!$D$2:$D$9<=45),--(Sheet1!$A2:$
A9=$A2),Sheet1!$B$2:$B$9)

for 31-45

copy down

in D2

=SUMPRODUCT(--(Sheet1!$D$2:$D$9>45),--(Sheet1!$D$2:$D$9<=60),--(Sheet1!$A2:$
A9=$A2),Sheet1!$B$2:$B$9)

for 46-60

copy down

then I assume 61-90 etc



--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[The Grinch]

maybe a pivot table would help

 

[S. Obaid]

Dear Peo.....
Thanks a lot........ it worked perfectly....
thanx for your help.. I would request you to
plz comment on my other post " RE: HEADING
CHANGES AS FILTERED DATA CHANGES"
posted on July 7th, 2004.

Thanx for your help..

Syed Obaid