Using SUMIF function with multiple criteria for Aging

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

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)

"josnah" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
> 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!
>
>
> --
> josnah
> ------------------------------------------------------------------------
> josnah's Profile:
http://www.excelforum.com/member.php...fo&userid=6334
> View this thread: http://www.excelforum.com/showthread...hreadid=548241
>