Skip to main content

Master Excel with SUMIF – Learn and Practice with Free Downloadable File!

Welcome to my blog!

Excel is a powerful tool, but its real magic lies in its functions. In this post, I’ll walk you through three essential Excel functions: IF, VLOOKUP, and SUMIF. Whether you’re a beginner or looking to sharpen your skills, this guide is perfect for you. Plus, I’ve included a downloadable Excel file for hands-on practice!

1. The IF Function: Making Decisions in Excel

The IF function helps you apply logic to your data by making decisions based on conditions. It checks whether a condition is true or false, then performs one action for TRUE and another for FALSE.

Syntax

excel
=IF(logical_test, value_if_true, value_if_false)
  • logical_test: The condition to evaluate (e.g., A1 > 50).
  • value_if_true: The result if the condition is TRUE.
  • value_if_false: The result if the condition is FALSE.

Examples

Bonus Eligibility

Next, in Column F, we will determine who is eligible for a bonus. For this, we will use the IF function.
Formula:

excel
=IF(C2 >= D2, "Eligible", "Not Eligible")

Explanation of the IF Function:

  • If the Sales (C2) are greater than or equal to the Target (D2), the formula will display "Eligible".
  • Otherwise, it will display "Not Eligible".

Steps to Follow:

  1. Enter this formula in the first cell of Column F.
  2. Press Enter to see the result.
  3. Drag the formula down to the last employee (Zane).

Bonus Amount

Finally, in Column G, we will calculate the bonus amount using a Nested IF function.
Formula:

excel
=IF(C2 >= D2, IF(E2 >= 120%, 10000, 5000), 0)

Explanation of the Nested IF Function:

  • First, it checks if the Sales (C2) are greater than or equal to the Target (D2).
    • If true, it evaluates the second condition:
      • If Target Achieved (E2) is greater than or equal to 120%, the bonus will be ₹10,000.
      • Otherwise, the bonus will be ₹5,000.
  • If false, the bonus will be ₹0.

Steps to Follow:

  1. Enter this formula in the first cell of Column G.
  2. Press Enter to see the result.
  3. Drag the formula down to the last employee (Zane).

Other Example

  1. Pass/Fail Example:
    To check if a student scored more than 50:

    excel
    =IF(A1>50, "Pass", "Fail")

  2. Discount Example:
    If sales exceed $1000, apply a discount:

    excel
    =IF(B1>1000, B1*0.1, 0)

Why Use It?

  • Automates decisions in Excel.
  • Useful for applying conditional logic to your data.

2. The VLOOKUP Function: Finding Data Easily

The VLOOKUP function helps you search for a value in the first column of a table and return a corresponding value from another column. It's great for organizing and retrieving data quickly.

Syntax

excel
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value: The value to search for.
  • table_array: The table containing the data.
  • col_index_num: The column number in the table from which to retrieve data.
  • range_lookup: Use FALSE for an exact match or TRUE for an approximate match.

Examples

  1. Product Price Example:
    To find the price of a product from a table:

    excel
    =VLOOKUP("Apples", A2:C10, 2, FALSE)

  2. Employee Details Example:
    Retrieve an employee’s department based on their ID:

    excel
    =VLOOKUP(101, A2:E20, 3, FALSE)

Why Use It?

  • Saves time when working with large datasets.
  • Ideal for creating lookup tables and organizing data.

3. The SUMIF Function: Conditional Summing

The SUMIF function adds values based on a specific condition. It’s perfect for financial reports, sales data, and other analyses where you need to calculate totals based on criteria.

Syntax

excel
=SUMIF(range, criteria, [sum_range])
  • range: The cells to evaluate the condition.
  • criteria: The condition to meet (e.g., ">50", "Apples").
  • sum_range: The cells to sum if the condition is true.

Examples

  1. Sales Example:
    Add sales for "Apples":

    excel
    =SUMIF(A2:A10, "Apples", B2:B10)

  2. Threshold Example:
    Sum all values greater than 100:

    excel
    =SUMIF(B2:B10, ">100")

Why Use It?

  • Makes summing specific data effortless.
  • Great for filtering data and calculating targeted totals.

Download the Practice File

To master these functions, download my free Excel practice file, which includes:

  • Sample datasets for each function.
  • Pre-set examples and exercises.
  • A blank sheet for your experiments.

Click Here to Download the Excel File (Attach or link the file via Google Drive or Dropbox.)

Watch the YouTube Video

Don’t forget to check out my detailed YouTube tutorial on IF, VLOOKUP, and SUMIF functions. Watch it here: YouTube Link.

Conclusion

Mastering IF, VLOOKUP, and SUMIF will significantly boost your Excel skills and efficiency. With these functions, you can handle complex tasks like a pro!

Got questions or feedback? Drop a comment below or reach out. Let’s learn together!

Stay Connected

Comments

Post a Comment

Popular posts from this blog

Tata Motors Limited Q3FY25 Concoll Summary

  Tata Motors Limited Tata Motors Limited (TTMT) Q3 FY25 Earnings Call Highlights January 29, 2025 | Morgan Stanley Equity Research Key Highlights from the Conference Call Tata Motors Limited (TTMT) reported steady performance across its business segments, supported by robust execution, market resilience, and strategic focus on financial discipline. The company demonstrated sustained progress in cost optimization, margin expansion, and strengthening its EV and JLR business. 1. Group Financial Performance Consolidated revenue grew by 2.7% YoY , reflecting strong performance in the passenger vehicle (PV) and Jaguar Land Rover (JLR) segments. EBIT margin expanded by 60 bps , driven by better mix, cost efficiency, and premiumization trends. Profit Before Tax (PBT) remained flat YoY but showed sequential improvement, reflecting operational efficiency and financial prudence. Free cash flow (FCF) stood at ₹4,700 crores , enabling a reduction in net debt to ₹19,200 crores . PBT u...
Post a Comment
Read more

Titan Company Limited Q3FY25 Concoll Summary

Titan Company Limited  Titan Company Limited – Q3 FY25 Earnings Conference Call: Key Highlights Overview Titan Company Limited reported a strong Q3 FY25 performance , demonstrating robust revenue growth across key business segments , driven by strong buyer growth, premiumization, and a disciplined competitive strategy . The Jewellery division continued to outperform , with same-store growth improving from 15% in Q2 to 22% in Q3 . Watches & Wearables also delivered double-digit growth , while CaratLane and emerging businesses like Taneira saw healthy momentum . Despite volatility in gold prices and competitive pressures , Titan remains focused on long-term growth, brand investments, and operational excellence . Management reiterated their confidence in sustaining double-digit revenue growth with an annual EBIT margin target of 11%–11.5% . Financial & Operational Performance Jewellery Revenue Growth: Strong quarter, with gold jewellery outperforming studded jewellery...
Post a Comment
Read more

Angel One Limited Q1 FY25 Earnings Call Summary

Angel One Limited Q1 FY25 Earnings Call Summary Executive Summary Angel One Limited has demonstrated strong financial and operational performance in Q1 FY25, driven by strategic investments in technology, marketing, and product diversification. The company remains well-positioned to navigate regulatory changes and continue its growth trajectory. Below is a comprehensive summary of the key points discussed during the earnings call. Financial Performance Angel One reported a gross revenue of ₹14.1 billion, showcasing sequential growth. The average daily orders sustained at 7.7 million, with total orders reaching 462 million. The gross broking revenue was maintained at ₹9.2 billion, contributing 65% to the total gross revenues. Segment-wise, F&O contributed 84% of gross broking revenue, the cash segment 11%, and the commodity segment 5%. The client base expanded to over 24 million, predominantly from Tier 2 and Tier 3 cities, executing 46 million orders with an ADTV of nearly ₹44 tril...
Post a Comment
Read more