Need either VBA or formulas to count and then sum up range value in a column

[tcambridge]

I have data table as below:



Transaction ID
Amt


11012952
2,260.80


11003036
1,836.00


10009737
100,678.44


11022022
50,601.00


11007008
1,495.20


11021090
13,910.00


11021974
1,370.64


102093
32,364.00


11016091
21,062.04


11020050
1,255.80


11021877
21,141.80


10003237
1,216.44


107981
1,206.36


10005983
11,922.00


11004947
1,143.00


10009452
1,100.40


11012417
9,014.88


10013307
1,068.00


102125
1,057.40


11012787
10,057.00


10010351
1,055.04


11021649
10,051.20


11008182
1,048.08


10006068
1,020.48


10005577
1,000.20


10006709
9.93


10011042
990.48


10011652
979.56


108300
964.44


11020745
91.60



I need to count number of transactions in four different range of values: >=50K, <50K>=30K, <30K>=10K, and less than 10K. Then I need the total transaction amount for each range of value above.



Transaction Level
#
$$


$50k +




$30k to $50k




$10k to $30k




$0k to $10k




Total
-
$ -



I wonder if using VBA code or formula will be help me to expedite the the process while yield the most accurate result. Thanks for all your helps.

[mancubus]

try:

formulas in G15:G18

=COUNTIFS(D:D,">=50000")
=COUNTIFS(D:D,">=30000",D:D,"<50000")
=COUNTIFS(D:D,">=10000",D:D,"<30000")
=COUNTIFS(D:D,">=0",D:D,"<10000")

formulas in H15:H18

=SUMIFS(D:D,D:D,">=50000")
=SUMIFS(D:D,D:D,">=30000",D:D,"<50000")
=SUMIFS(D:D,D:D,">=10000",D:D,"<30000")
=SUMIFS(D:D,D:D,">=0",D:D,"<10000")

$50k + = 50000 - 9.9E307
$30k to $50k = $30000 - $49999
$10k to $30k = $10000 - $29999
$0k to $10k = $0 - $9999

[tcambridge]

Thank you So much. It works great!

[mancubus]

you are welcome. please mark the thread as solved from thread tools dropdown.