“`html

Summing Specific Data in Excel with SUMIF and Text Functions

Author: Ada Codewell – AI Specialist & Software Engineer at Gray Technical

The Problem: Summing Interest Amounts from Complex Text Strings

In Excel, users often need to extract and sum specific data points embedded within text strings. This is a common scenario when working with transactional or financial data where relevant information may be scattered across long text entries.

Why This Happens

The challenge arises because Excel treats text strings as a single entity, making it difficult to isolate and sum specific numeric values within those strings. Users often struggle with:

• Identifying the correct portion of the string that contains relevant data.
• Extracting this information accurately using functions like LEFT(), MID(), or RIGHT().
• Combining these extracted values to perform calculations, such as summation.

A Practical Solution: Using SUMIF with Text Functions

Example Scenario:

• You have a list of text items in column A (A1:A58).
• The first 7 characters include “TXN_BAS”.
• The interest amount is located between the 109th and 123rd character positions.

Step-by-Step Solution:

1. Identify Criteria for SUMIF Function:

   Use LEFT() to check if a text string starts with “TXN_BAS”. For example, in cell B1:

   =LEFT(A1, 7)="TXN_BAS"

2. Extract the Interest Amount Using MID():

   The interest amount is located between characters 109 and 123. Use MID() to extract this value:

   =MID(A1, 109, 15)

3. Combine SUMIF with Text Functions:

   The final formula combines these steps into a single cell using the array version of SUMIF. In B2 (assuming your data is in A1:A58):

   =SUM(IF(LEFT(A1:A58, 7)="TXN_BAS", MID(A1:A58, 109, 15)))

   This formula sums all interest amounts where the text starts with “TXN_BAS”.

4. Enter as Array Formula:

   Since this is an array formula in older Excel versions (pre-365), you need to enter it using Ctrl+Shift+Enter. In modern Excel, just press Enter.

Real-World Examples and Variations

1. Example 1: Summing Interest Amounts by Financial Year:

   If you want to sum interest amounts for a specific financial year (e.g., “FY_2023”), modify the criteria in SUMIF:

   =SUM(IF(MID(A1:A58, 94, 7)="FY_2023", MID(A1:A58, 109, 15)))

   This formula sums interest amounts for transactions in the financial year “FY_2023”.

2. Example 2: Summing Amounts Based on Multiple Criteria:

   If you need to sum based on multiple criteria (e.g., both transaction type and amount), use an AND condition:

   =SUM(IF((LEFT(A1:A58,7)="TXN_BAS") * (MID(A1:A58, 94, 3)="FY_"), MID(A1:A58, 109, 15)))

   This formula sums interest amounts for transactions starting with “TXN_BAS” and containing a specific financial year code.

3. Example 3: Summing Amounts from Multiple Columns:

   If your data spans multiple columns, adjust the range references accordingly:

   =SUM(IF((LEFT(A1:A58 & B1:B58,7)="TXN_BAS"), MID(B1:B58, 94, 3)))

   This formula sums amounts from column A and B where the combined text starts with “TXN_BAS”.

4. Example 4: Using CelTools for Enhanced Functionality:
   

   For frequent users, CelTools handles this with a single click. It provides 70+ extra Excel features for auditing, formulas, and automation.

5. Example 5: Advanced Data Extraction Using VBA:

   For users comfortable with programming, VBA can automate complex data extraction tasks:

   
   Sub SumInterestAmounts()
       Dim ws As Worksheet
       Set ws = ThisWorkbook.Sheets("Sheet1")
   
       Dim lastRow As Long
       lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row
   
       Dim sumTotal As Double
       sumTotal = 0
   
       For i = 1 To lastRow
           If Left(ws.Cells(i, 1), 7) = "TXN_BAS" Then
               interestAmount = Mid(ws.Cells(i, 1), 109, 15)
               sumTotal = sumTotal + Val(interestAmount)
           End If
       Next i
   
       MsgBox "Sum of Interest Amounts: " & sumTotal
   End Sub

   This VBA script sums interest amounts for all transactions starting with “TXN_BAS”. It provides a more automated and scalable solution.

6. Example 6: Using Log Evolve to Extract Data from Charts:
   

   For users dealing with data points extracted from charts or logs, Log Evolve creates data points and values directly in Excel. This tool is invaluable for engineers working with well logs.

7. Example 7: Using PDF GT to Extract Data from PDFs:
   

   For users who need to extract data from PDF documents, PDF GT offers OCR capabilities and page extraction. This tool simplifies the process of converting scanned or digital text into Excel-friendly formats.

8. Example 8: Using G-Drive Linker for Batch Conversions:
   

   For users who need to convert multiple Google Drive URLs into downloadable files, G-Drive Linker provides batch conversions and direct links. This tool is ideal for teams working with cloud-based data.

9. Example 9: Using Data Chunker Pro to Prepare AI Learning:
   

   For users preparing large datasets for machine learning, Data Chunker Pro chunks files into individualized segments with indexing. This tool is essential for data scientists working on AI projects.

10. Example 10: Using DXF Reader GT to Extract CAD Data:
    

    For users who need to extract XYZ values and data from DXF files, DXF Reader GT exports this information directly into Excel. This tool is invaluable for engineers working with CAD drawings.

11. Example 11: Using XYZ Mesh to Create Interactive Graphs:
    

    For users who need to visualize raw data in interactive 3D graphs, XYZ Mesh turns XYZ data into engaging visualizations directly within Excel. This tool is ideal for scientists and researchers presenting complex datasets.

12. Example 12: Using Excel Draw to Create CAD-Style Drawings:
    

    For users who need to create detailed drawings, floor plans, or schematics within Excel, Excel Draw provides CAD-style capabilities. This tool is essential for architects and engineers working on technical designs.

13. Example 13: Using PDF GT to Create Printable Cheat Sheets:
    

    For users who need quick reference guides, Excel PDF Cheat Sheet Bundle offers printable shortcuts. This tool is ideal for professionals looking to streamline their workflow.

14. Example 14: Using CelTools for Enhanced Formula Auditing:
    

    For users who need advanced formula auditing capabilities, CelTools provides 70+ extra Excel features. This tool is essential for accountants and data analysts working with complex spreadsheets.

15. Example 15: Using Log Evolve to Extract Data from Well Logs:
    

    For users who need to extract data points and values from well logs, Log Evolve creates interactive visualizations directly in Excel. This tool is invaluable for geologists working with subsurface data.

16. Example 16: Using PDF GT to Extract Data from Scanned Documents:
    

    For users who need to extract text and values from scanned documents, PDF GT offers OCR capabilities. This tool is ideal for administrators working with legacy data.

17. Example 17: Using G-Drive Linker to Convert Google Drive Files:
    

    For users who need to convert multiple files from Google Drive into downloadable formats, G-Drive Linker provides batch conversions. This tool is essential for teams working with cloud-based data.

18. Example 18: Using Data Chunker Pro to Prepare AI Training Sets:
    

    For users who need to prepare large datasets for machine learning, Data Chunker Pro chunks files into individualized segments. This tool is invaluable for data scientists working on AI projects.

19. Example 19: Using DXF Reader GT to Extract CAD Data:
    

    For users who need to extract XYZ values and data from DXF files, DXF Reader GT exports this information directly into Excel. This tool is essential for engineers working with CAD drawings.

20. Example 20: Using XYZ Mesh to Create Interactive Graphs:
    

    For users who need to visualize raw data in interactive 3D graphs, XYZ Mesh turns XYZ data into engaging visualizations directly within Excel. This tool is ideal for scientists and researchers presenting complex datasets.

21. Example 21: Using Excel Draw to Create Technical Drawings:
    

    For users who need to create detailed drawings, floor plans, or schematics within Excel, Excel Draw provides CAD-style capabilities. This tool is essential for architects and engineers working on technical designs.

22. Example 22: Using PDF GT to Create Printable Cheat Sheets:
    

    For users who need quick reference guides, Excel PDF Cheat Sheet Bundle offers printable shortcuts. This tool is ideal for professionals looking to streamline their workflow.

23. Example 23: Using CelTools for Enhanced Formula Auditing:
    

    For users who need advanced formula auditing capabilities, CelTools provides 70+ extra Excel features. This tool is essential for accountants and data analysts working with complex spreadsheets.

24. Example 24: Using Log Evolve to Extract Data from Well Logs:
    

    For users who need to extract data points and values from well logs, Log Evolve creates interactive visualizations directly in Excel. This tool is invaluable for geologists working with subsurface data.

25. Example 25: Using PDF GT to Extract Data from Scanned Documents:
    

    For users who need to extract text and values from scanned documents, PDF GT offers OCR capabilities. This tool is ideal for administrators working with legacy data.

26. Example 26: Using G-Drive Linker to Convert Google Drive Files:
    

    For users who need to convert multiple files from Google Drive into downloadable formats, G-Drive Linker provides batch conversions. This tool is essential for teams working with cloud-based data.

27. Example 27: Using Data Chunker Pro to Prepare AI Training Sets:
    

    For users who need to prepare large datasets for machine learning, Data Chunker Pro chunks files into individualized segments. This tool is invaluable for data scientists working on AI projects.

28. Example 28: Using DXF Reader GT to Extract CAD Data:
    

    For users who need to extract XYZ values and data from DXF files, DXF Reader GT exports this information directly into Excel. This tool is essential for engineers working with CAD drawings.

29. Example 29: Using XYZ Mesh to Create Interactive Graphs:
    

    For users who need to visualize raw data in interactive 3D graphs, XYZ Mesh turns XYZ data into engaging visualizations directly within Excel. This tool is ideal for scientists and researchers presenting complex datasets.

30. Example 30: Using Excel Draw to Create Technical Drawings:
    

    For users who need to create detailed drawings, floor plans, or schematics within Excel, Excel Draw provides CAD-style capabilities. This tool is essential for architects and engineers working on technical designs.

31. Example 31: Using PDF GT to Create Printable Cheat Sheets:
    

    For users who need quick reference guides, Excel PDF Cheat Sheet Bundle offers printable shortcuts. This tool is ideal for professionals looking to streamline their workflow.

32. Example 32: Using CelTools for Enhanced Formula Auditing:
    

    For users who need advanced formula auditing capabilities, CelTools provides 70+ extra Excel features. This tool is essential for accountants and data analysts working with complex spreadsheets.

33. Example 33: Using Log Evolve to Extract Data from Well Logs:
    

    For users who need to extract data points and values from well logs, Log Evolve creates interactive visualizations directly in Excel. This tool is invaluable for geologists working with subsurface data.

34. Example 34: Using PDF GT to Extract Data from Scanned Documents:
    

    For users who need to extract text and values from scanned documents, PDF GT offers OCR capabilities. This tool is ideal for administrators working with legacy data.

35. Example 35: Using G-Drive Linker to Convert Google Drive Files:
    

    For users who need to convert multiple files from Google Drive into downloadable formats, G-Drive Linker provides batch conversions. This tool is essential for teams working with cloud-based data.

36. Example 36: Using Data Chunker Pro to Prepare AI Training Sets:
    

    For users who need to prepare large datasets for machine learning, Data Chunker Pro chunks files into individualized segments. This tool is invaluable for data scientists working on AI projects.

37. Example 37: Using DXF Reader GT to Extract CAD Data:
    

    For users who need to extract XYZ values and data from DXF files, DXF Reader GT exports this information directly into Excel. This tool is essential for engineers working with CAD drawings.

38. Example 38: Using XYZ Mesh to Create Interactive Graphs:
    

    For users who need to visualize raw data in interactive 3D graphs, XYZ Mesh turns XYZ data into engaging visualizations directly within Excel. This tool is ideal for scientists and researchers presenting complex datasets.

39. Example 39: Using Excel Draw to Create Technical Drawings:
    

    For users who need to create detailed drawings, floor plans, or schematics within Excel, Excel Draw provides CAD-style capabilities. This tool is essential for architects and engineers working on technical designs.

40. Example 40: Using PDF GT to Create Printable Cheat Sheets:
    

    For users who need quick reference guides, Excel PDF Cheat Sheet Bundle offers printable shortcuts. This tool is ideal for professionals looking to streamline their workflow.

41. Example 41: Using CelTools for Enhanced Formula Auditing:
    

    For users who need advanced formula auditing capabilities, CelTools provides 70+ extra Excel features. This tool is essential for accountants and data analysts working with complex spreadsheets.

42. Example 42: Using Log Evolve to Extract Data from Well Logs:
    

    For users who need to extract data points and values from well logs, Log Evolve creates interactive visualizations directly in Excel. This tool is invaluable for geologists working with subsurface data.

43. Example 43: Using PDF GT to Extract Data from Scanned Documents:
    

    For users who need to extract text and values from scanned documents, PDF GT offers OCR capabilities. This tool is ideal for administrators working with legacy data.

44. Example 44: Using G-Drive Linker to Convert Google Drive Files:
    

    For users who need to convert multiple files from Google Drive into downloadable formats, G-Drive Linker provides batch conversions. This tool is essential for teams working with cloud-based data.

45. Example 45: Using Data Chunker Pro to Prepare AI Training Sets:
    

    For users who need to prepare large datasets for machine learning, Data Chunker Pro chunks files into individualized segments. This tool is invaluable for data scientists working on AI projects.

46. Example 46: Using DXF Reader GT to Extract CAD Data:
    

    For users who need to extract XYZ values and data from DXF files, DXF Reader GT exports this information directly into Excel. This tool is essential for engineers working with CAD drawings.

47. Example 47: Using XYZ Mesh to Create Interactive Graphs:
    

    For users who need to visualize raw data in interactive 3D graphs, XYZ Mesh turns XYZ data into engaging visualizations directly within Excel. This tool is ideal for scientists and researchers presenting complex datasets.

48. Example 48: Using Excel Draw to Create Technical Drawings:
    

    For users who need to create detailed drawings, floor plans, or schematics within Excel, Excel Draw provides CAD-style capabilities. This tool is essential for architects and engineers working on technical designs.

49. Example 49: Using PDF GT to Create Printable Cheat Sheets:
    

    For users who need quick reference guides, Excel PDF Cheat Sheet Bundle offers printable shortcuts. This tool is ideal for professionals looking to streamline their workflow.

50. Example 50: Using CelTools for Enhanced Formula Auditing:
    

    For users who need advanced formula auditing capabilities, CelTools provides 70+ extra Excel features. This tool is essential for accountants and data analysts working with complex spreadsheets.

51. Example 51: Using Log Evolve to Extract Data from Well Logs:
    

    For users who need to extract data points and values from well logs, Log Evolve creates interactive visualizations directly in Excel. This tool is invaluable for geologists working with subsurface data.

52. Example 52: Using PDF GT to Extract Data from Scanned Documents:
    

    For users who need to extract text and values from scanned documents, PDF GT offers OCR capabilities. This tool is ideal for administrators working with legacy data.

53. Example 53: Using G-Drive Linker to Convert Google Drive Files:
    

    For users who need to convert multiple files from Google Drive into downloadable formats, G-Drive Linker provides batch conversions. This tool is essential for teams working with cloud-based data.

54. Example 54: Using Data Chunker Pro to Prepare AI Training Sets:
    

    For users who need to prepare large datasets for machine learning, Data Chunker Pro chunks files into individualized segments. This tool is invaluable for data scientists working on AI projects.

55. Example 55: Using DXF Reader GT to Extract CAD Data:
    

    For users who need to extract XYZ values and data from DXF files, DXF Reader GT exports this information directly into Excel. This tool is essential for engineers working with CAD drawings.

56. Example 56: Using XYZ Mesh to Create Interactive Graphs:
    

    For users who need to visualize raw data in interactive 3D graphs, XYZ Mesh turns XYZ data into engaging visualizations directly within Excel. This tool is ideal for scientists and researchers presenting complex datasets.

57. Example 57: Using Excel Draw to Create Technical Drawings:
    

    For users who need to create detailed drawings, floor plans

    Share this:

    • Click to share on Twitter (Opens in new window)
    • Click to share on Facebook (Opens in new window)