Use of SUMIF function in excel

Writing an article to appraise readers, how they can use SUMIF in analyzing the financial data.

Use of SUMIF in excel:

♠ SUMIF is used to sum up the values of a given range as per the specified criteria.

♠ For example: You are working on GST liability computation and analyzing your ITC register to find out the eligible and ineligible ITC. Using SUMIF function, you can get eligible ITC figure from the ITC register, which consist of both eligible and ineligible invoices.

♠  Since, ITC register may consists of both ineligible and eligible ITC, as can be seen from a small extract of a typical ITC register (no. of fields of ITC register extracted from an ERP runs into more than 50).

How to calculate the figure of eligible ITC just by applying a formula. Here, is the use of SUMIIF

To calculate sum of Eligible CGST ITC (eligible ITC invoices marked as Yes ineligibility column)

Formula: SUMIF (Range, Criteria, Sum Range)

  • Range means the column where the criteria to be used is mentioned. In our case, column “N”, where ITC eligibility criteria has been mentioned as “Yes” (for Eligible), “No” for ineligible. Therefore, our range is N2 to N12
  • Criteria means the condition on which SUMIF has to work i.e. Eligibility – Yes/No
  • Sum Range means the column the values of which has to be summed up i.e. ITC columns –CGST, SGST and IGST

The use of formula has been depicted by the screenshots below.

  • Locking the reference- It is always advisable to lock the reference while applying any formula, particularly when we are working on large set of data. Reference to be locked for cells which indicates a range like column K (CGST) which is sum range i.e. the sum of eligible CGST amount against various invoices. Reference can be locked by pressing F4 key between starting and ending point of any range. In our case, if we have to lock the reference of range K2 to K11, then put cursor anywhere at K2 and press F4 and also anywhere at K11 and press F4. Locked reference cells can be seen as cells with dollar symbol. The same has been depicted below where the reference has been locked while applying SUMIF formula to work out sum of eligible CGST:

  • This way we can apply SUMIF formulas separately for CGST, SGST and IGST to work out the eligible ITC amount to be used for calculating GST liability.
  • Readers may wonder that why to use SUMIF formulas, when we can simply get the figure of eligible ITC by filtering with Eligibility column.
  • Here is the answer- While prepare GST workings, we analyze our sales, ITC, RCM and other data to work out the liability and ITC. Therefore, we need to prepare a summary of our workings to arrive at the final figure of liability & ITC. Below is the general format of summary.
  • We can use SUMIF formula, to bring the Eligible ITC figure directly into this summary which can be traced back to the ITC register (because formulas can be traced back to their source). There will not be a requirement to enter the figures manually. Thus, accuracy of calculation can be ensured.
Summary- Liability and ITC availment & utilization
Particulars CGST SGST IGST Cess
Tax payable                            –
Less Input tax credit
i) Imported  goods
ii) Domestic inputs and input services  =sumif(ITC register……..)  =sumif(ITC register……..)   =sumif(ITC register……..)  =sumif(ITC register……..)
iii) ITC of Reverse charge paid in Aug 18
Tax  on output supplies paid in cash                              –                        –                             –
  • SUMIF can also be used while working on GSTR-1, particularly for calculating invoice value of invoices having multiple tax rates as we have to report the full invoice value against every tax rate line item. By using SUMIF, we can work out invoice value against every tax rate line item as required to reported.

Excel Tips:

  • ALT+HOR- We can rename excel sheet by using this shortcut, so next time don’t use mouse for renaming the sheet.
  • CTRL+SHIFT+3- If by copy- pasting, if “date” column has got converted into numbers, don’t worry, just use this formula. It will bring back your date format. (CTRL, SHIFT AND 3 keys of left hand side of key board to be used).

Download Excel File with SUMIF function

Thanks for reading!! Your feedback is most welcomed at saurabhchokhra92@gmail.com.



Privacy Settings