Sum the values in a column except the header

All we need is an easy explanation of the problem, so here it is.

I have a file as given below

--------------------------------------------------------------
Name_Customer   Item_Purchased  Item_Amount Credit
--------------------------------------------------------------
Tom              H1_P            7657        N/A    
Pras             Track_1         23          N/A
Cha              Brace           9           N/A
Moh              kite37          269         N/A
Prab             Bols            87699       N/A

I need to add the values under the column Item_Amount by ignoring the header in the file and print the sum as

Total Amount collected = 95657

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

awk '{s+=$3}END{print s}' yourfile

Method 2

Pretty trivial using just awk. Assuming the example data is in a file, ex.txt:

$ awk '{total = total + int($3)}END{print "Total Amount collected = "total}' ex.txt

Example

$ awk '{total = total + $3}END{print "Total Amount collected = "total}' ex.txt 
Total Amount collected = 95657

Details

Using awk we collect the values from the 3rd column ($3) and accumulate their sub-total in the variable total. Once complete, as the last thing to do, END{..}, we print the message along with the value of the variable total.

Method 3

total=0; 
for n in  $( tail -n +4 /tmp/reports.txt | awk '{print $3}') ; 
do 
   total=$( expr $total + $n ); 
done ; 
echo ">>$total" 

Method 4

The awk approach is probably the easiest. Here are a few other choices:

Perl:

perl -lane '$k+=$F[2];END{print $k}' foo.txt

Pure coreutils:

t=0; tail -n +4 foo.txt | tr -s ' ' '\t' | cut -d $'\t' -f 3 | 
 while read i; do let t+=$i; echo $t; done  | tail -n 1

Method 5

If it can help:

grep -Eo '[0-9\.]+' your_file|tr '\n' '+'|sed 's/\+$//'|bc -l

Method 6

This pipeline should do the work:

tail -n +4 the_file | awk '{ sum += $3 } END { printf "Total Amount collected = %d\n", sum }'

Method 7

awk '{FS=","}{s+=$6}END{print s}' Filename

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply