Formulas for counting and summing

[jcowans]

Need two formulas. #1-counts all "2002" transactions (A:A) that are aged "0-30"(B:B). #2-sum total dollar values (C:C) of all transaction from formula #1.

FY Full Age Amount
2002 0-30 $100.00
2002 0-30 35.00
2001 61-90 25.00
2001 0-30 354.00
2000 >365 125.00

For 2002 transactions aged 0-30, results should be :
2002=2
Age sum= $135.00

 

[Aladin Akyurek]

On 2002-09-06 18:34, jcowans wrote:
Need two formulas. #1-counts all "2002" transactions (A:A) that are aged "0-30"(B:B). #2-sum total dollar values (C:C) of all transaction from formula #1.

FY Full Age Amount
2002 0-30 $100.00
2002 0-30 35.00
2001 61-90 25.00
2001 0-30 354.00
2000 >365 125.00

For 2002 transactions aged 0-30, results should be :
2002=2
Age sum= $135.00

Let A2:C6 house the sample exclusing the labels.

To get the desired count:

=SUMPRODUCT(($A$2:$A$6=E1)*($B$2:$B$6=F1))

where E1 houses a condition like 2002 and F1 a condition like 0-30.

To get the desired sum:

=SUMPRODUCT(($A$2:$A$6=E1)*($B$2:$B$6=F1),$C$2:$C$6)

for the same conditions as for the counting.

You might also want to consider a pivot table from your data to obtain the same results.

 

[jcowans]

Thanks for your help on my excel problem.