Archive for March 20, 2020

Excel, VBA Codes and Formulas-2

March 20, 2020 1 comment

Learn To : In Excel – SUMIF and SUMIFS [ Part-1 SUMIF ]

Case Assumption: Assume we have a table, with 5 columns (as in image-1) columns titles are [Date, Item, Quantity, Price and Total] containing our Expendituer and we have another table call it Summaries, we want some totals to be in this table. In Image-1 we can see the main table [Expendituer] and the Summary table.


In the Above Expendituer Table we have some [Dummy] data, and in our Summary Table we want to calculate the SUM of each year so in the Cell J22 we shall write this formula: =SUMIF()
SUMIF will take 3 parameters
First one is the range of applying the condition.
Second one is the condition.
Third one is the range to calculate to SUM form. So in our case [Image-1]

The range of applying the condition will be C22:C28

The condition is in Cell I22 [23,24,25 … for each row and year.]

The range to calculate to SUM form will be G22:G28

So in Cell J22 we will write =SUMIF(C22:C28,”*”&I22,G22:G28)

then we can copy the formula to other cells [I23 and I24]

So now we have the Sum in each Year. See Image-2


Now with same formula SUMIF, we can get the Quantity of each Item we purchase, and we will write the formula in Cell J27 [as in Image-1].

So in Cell J27 we will write =SUMIF(D22:D28,I27,E22:E28) as in Image-3 then we copy the formula to Cells [J28, J29, J30 .. and so]


Now we have the sum of Quantity.

🙂 Have Fun ..

Follow me on Twitter..

By: Ali Radwani