Using SUMIF function with multiple criteria for Aging

  • Thread starter Thread starter josnah
  • Start date Start date
J

josnah

I have the following data which needs to be analysed into an aging
table.


TRN MO NET AMT
Adj 1 (2,189.03)
Adj 4 (62.49)
Jrn 4 302.40
Jrn 10 (989.69)
Jrn 2 1.24
Rcp 5 (366.86)
Rcp 10 (1,807.05)
Rcp 11 1,447.20
Rcp 3 (2,409.75)
New 1 20.00
New 4 45.18
New 6 60.00
End 9 804.28
Adj 9 (431.25)
Rcp 1 (873.60)
New 8 2,430.00
New 7 1,153.87
New 15 472.48
New 14 208.71
Rcp 7 (4,291.34)

* MO = Months Outstanding

The aging table is as follows:

ANALYSIS
NET AMT RCP
1 MONTHS
2 - 3 MONTHS
4 - 6 MONTHS
7 - 9 MONTHS
10 - 12 MONTHS
12 MONTHS
Click to expand...

:confused: I am able to use SUMIF to calculate the Net Amt for 1 mth
but how do I get the Amt for *Rcp *alone?
Also how do I use sumif to calculate *Net Amt & Receipts *which are
outstanding for (e.g.) *2-3 months*?

Any help is greatly appreciated! :)
 
Hi,

I'm not quiet sure this is what you are looking for but anyways.

Maybe this will help you on the first one. This Sumproduct will give
you the sum of the combinations where TRN is Rcp and MO = 1

=SUMPRODUCT(--(A2:A21="Rcp"),--(B2:B21=1),C2:C21)

If you want the sum for month 2 and 3 you could just make two and add
them.

Regards,
Bondi
 
=SUMPRODUCT(--(A2:A21="Rcp"),--(B2:B21=1),C2:C21)

and

=SUMPRODUCT(--(A2:A21="Rcp"),--(B2:B21>1),--(B2:B21<=3),C2:C21)

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Back
Top