SUM, SUBTOTAL, SUMPRODUCT, SUBSTITUTE, SUMIF and SUMIFS formulas in Excel

 Excel SUM Series

Here’s a breakdown of the SUM, SUBTOTAL, SUMPRODUCT, SUBSTITUTE, SUMIF  
and SUMIFS formulas in Excel, including their usage and examples

  • Let's go over each of these Excel functions with examples to help you understand their use:

    1. SUM

    2. SUBTOTAL

    The SUBTOTAL function returns a subtotal in a list or database. It can perform various

    operations like SUM, AVERAGE, COUNT, etc.

    Formula: SUBTOTAL(function_num, ref1, [ref2], ...)

    Example: =SUBTOTAL(1,H2:H101)

   

    3. SUMPRODUCT

    The SUMPRODUCT function multiplies corresponding components in the given arrays and 

    then sums the products.

    Formula: SUMPRODUCT(array1, [array2], ...)

    Example: =SUMPRODUCT(G2:H101)


    4. SUBSTITUTE

    The SUBSTITUTE function substitutes new text for old text in a text string.

    Formula: SUBSTITUTE(text, old_text, new_text, [instance_num])

    Example: =SUBSTITUTE(K12,"Great","Hitesh")


    5. SUMIF

    The SUMIF function adds the cells specified by a given condition or criteria.

    Formula: SUMIF(range, criteria, [sum_range])

    Example: =SUMIF(E:E,K15,H:H)


    6. SUMIFS

    The SUMIFS function adds the cells specified by a set of conditions or criteria.

    Formula: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

    Example: =SUMIFS(H:H,E:E,K19,F:F,K20)


Would you like to dive deeper into any of these functions or have specific use cases in mind?


Comments

Post a Comment

Popular posts from this blog

Current Date and Time for Excel Formulas and Functions 2024 in Hindi and Gujarati | Excel Formula Tutorial | Excel Formulas

Image
  Excel Formulas for Current Date and Time Excel provides easy-to-use formulas for inserting the current date, time, or both into a worksheet. Here are the formulas along with examples: 1. Current Time Only Formula Formula: =TEXT(NOW(), "HH:MM:SS AM/PM") or =TEXT(NOW(), "HH:MM:SS") for a 24-hour format. Automatic Updates: Both =TODAY() and =NOW() are dynamic and update automatically whenever the worksheet recalculates. Static Date and Time: If you want to insert the current date or time as a static value (that doesn’t change), you can press Ctrl + ; for the current date and Ctrl + Shift + ; for the current time. Description: This formula extracts and formats the current time from the NOW() function. Example: If the current time is 11:55 AM, entering =  Ctrl + Shift + ;  press  will display 11:55:00 AM. https://youtu.be/8sM9GWmZHGU 2. Current Date Formula Formula: =TODAY() Description: This formula returns the current date. The date is updated every time th...
Read more
  Microsoft excel basic tools knowledge 1: Introduction to Excel Interface 1.1 Excel Interface Overview Image: Excel home screen with main sections labeled (Ribbon, Workbook, Worksheet, Formula Bar, etc.) Details: Brief description of each section. 1.2 Ribbon and Tabs Home Tab: Font Group: Image: Font group highlighted. Details: Changing font style, size, color, and adding effects (bold, italic, underline). Alignment Group: Image: Alignment group highlighted. Details: Aligning text (left, center, right), merging cells, wrapping text. Number Group: Image: Number group highlighted. Details: Formatting numbers, currency, dates, percentages. 2: Basic Functions and Formulas 2.1 Basic Formulas SUM Function: Image: Example of using the SUM function. Details: Adding a range of cells (e.g., =SUM(A1:A10) ). AVERAGE Function: Image: Example of using the AVERAGE function. Details: Calculating the average of a range of cells (e.g., =AVERAGE(B1:B10) ). COUNT Function: Image: Example...
Read more

COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS formulas in Excel

Image
  Excel Count Series Here’s a breakdown of the COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS formulas in Excel, including their usage and examples 1. COUNT Formula: The COUNT formula is used to count the number of cells that contain numbers within a specified range. COUNT: Counts the number of cells with numbers. COUNT: =COUNT(F2:F21) http://www.youtube.com/@Jemal_Knowledge32 https://youtu.be/Xi6Q8ayeuNs https://www.linkedin.com/posts/hitesh-darji-11452b271_exceltutorial-exceltips-exceltraining-activity-7224774354536996864-fpWC?utm_source=share&utm_medium=member_android https://www.instagram.com/reel/C-IUSn9yMR3/?igsh=MWg3YmZybXZ0bWNuMg== COUNTA:=COUNT(C2:D13)   3. COUNTBLANK Formula The COUNTBLANK formula is used to count the number of empty cells within a specified range. COUNTBLANK: Counts the number of empty cells. COUNTBLANK: = COUNTBLANK(B1:I21) 4. COUNTIF Formula The COUNTIF formula counts the number of cells that meet a single specifi...
Read more