Home > excel, Learning, Lesson, Projects/Experiments > Excel, VBA Codes and Formulas-2

Excel, VBA Codes and Formulas-2


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.

Image-1

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

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]

Image-3


Now we have the sum of Quantity.

🙂 Have Fun ..



Follow me on Twitter..




By: Ali Radwani




  1. No comments yet.
  1. March 22, 2020 at 8:26 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: