How to use SUMIF Formula in excel

How to use SUMIF Formula in excel:

Want to do sum of key fields having thousands lines in few minutes ? SUMIF is very useful formula in excel. This Post will provide details regarding how to use SUMIF Formula in excel and  how to use remove duplicate function .

Use of SUMIF Over Pivot table:

It is very common practice to use Pivot Table when one requires to get sum of particular key field which are having records in thousands of raws in spreadsheet. e.g. if you want sales qty for few product segments , you may use Pivot . There is no doubt about benefit of Pivot table but it has some limitation in terms of getting real time data because once you change your source data , you have to refresh the pivot . SUMIF can change report in real time .

Understanding SUMIF Formula with example:

Remove Duplicate:

Before we use SUMIF , we have to use Remove Duplicate function . Kindly check below example, you can click on this link to download excel . Review file and follow below steps . You can click on image to view full screen picture .

1. You can see data in three column and 10 Raws.  Suppose you want to do sum of qty for a material name .
2. You have to first find out unique material name .
3. Copy A1:B10 and paste to E1:F10
4. Select E1:F10 > Click on Data Menu >Click on Remove Duplicates .
5. You will get below dialog box:

6.  By default , all columns are in “Select All” mode . You have to unselect all and select Material Name  . IF you are working on data having hundreds of heading , it is suggested to follow this step.

7. Click on ok , Duplicate Material Name (Raws) will be deleted . Only below items are available now:

8. You can see that duplicate material names are removed  (E1:F6)

How to use SUMIF Formula in excel:

1. If you want sum of qty for each Material , you have to use SUMIF formula
2. In case of SUMIF , you are giving whole range of data to excel to identify material name and do summation of qty of that material .
3. See above image 3 . SUMIF formula is bifurcated in three parts
   • B:B , you are giving reference of material name in source data .Instead you can give rage B1 to B10 however while coping formula to next cell , you have to freeze the range by $ i.e. $B$1:$B$10. I strongly recommend to use B:B however there should not be any duplicate data below table  . Insert “,” after range.
   • F2, You want to do summation for Material “Table” hence select F2 . Insert “,” after selection .
   • C:C, providing reference for related qty for a particular material name .
4. Yes you have done it .Simply you can paste formula for other four material name.
5. For a control check , you can see total is matching i.e. 317 for source data and summary data for material name.
6. You can use this method to use SUMIF on larger data .

Hope you must have now better clarity regarding how to use SUMIF formula in excel. Give your feedback in comment section .