Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Need help with a formula to sum of the values in column E and column F in every row (1 thru 11) if t

Copper Contributor

Device/OS System - Desktop PC - System Type 64bit OS x64based processor
Windows 10 Pro, version 22H2, installed 7/6/2022, OS Build 19045.3803
Excel Version - Installed as Part of Microsoft 365 - en - us, 16.0.17029.20108 on 12/14/2023

 

I am hopelessly stuck on something in Excel.

 

Need help with a formula to sum of the values in column E and column F in every row (1 thru 11) if the concatenation of column A and column C in that row meets the criteria in cell D13.

 

Using the table of values below, I want to sum the the values in column E and column F in every row (1 thru 11) if the concatenation of column A and column C in that row meets the criteria in cell D13.

 

If it worked properly, the total sum of values that meet the criteria would be shown in cell D13 would be 9175.15.

 

 ABCDEF
1Check  NATHCO 20221025 Vandenboom, Gene Deck Repair - Harris Ct         1,098.35                  -  
2Check  NATHCO 20221109 Carlson, Ashley Exterior Door           522.22                  -  
3Venmo  20221206 Drummond, Scott Basement Framing         1,300.04                  -  
4Bus Check  20230101 DeVuyst, Dick Garage 16120 Kenneth Rd         3,706.08                  -  
5Check  BAO 20230119 Mayhew, Barry Fence Repair         1,560.60                  -  
6Check  20230301 Pattin, Ann Shutters            (24.31)                  -  
7PayPal  20230301 Pattin, Ann General Interior I         2,099.30                  -  
8Bus Check  20230321 Lane, Jeremy Homesteader Cafe Drywall & Trim           479.37                  -  
9Check  NATHCO 20230322 Hopkins, Ruth Exterior Painting         7,554.58                  -  
10Venmo  20230322 Griffin, Cecily Exterior Door           735.90                  -  
11Bad Debt  20230605 Van Dyke, Heather Bathroom Repairs II (recorded bad debt for 654.94)                  -             654.94
12      
13   Check NATHCO<---- the term to meet, when evaluating every row, in order to be summed 
14   9175.15<---- the sum of the values in column E and column F in every row (1 thru 13) if the concatenation of column A and column C in that row meets the criteria in cell C39 

 

7 Replies

@Tamara Duvall 

 

Without seeing your actual workbook, it's hard to be certain about this, but you appear to have spaces before "NATHCO" in column C, so that when that's concatenated with column A it comes out as

"Check NATHCO" rather than "CheckNATHCO"

 

Check also to make sure your numbers are in fact numbers, rather than text masquerading as numbers.

 

And if you can, it would be far more helpful if you posted an actual workbook/spreadsheet. If you can't do it here--and you've been around long enough you might be able to--then use OneDrive or GoogleDrive and paste a link here that grants access.

@Tamara Duvall 

Rather than concatenating columns A and C (I have called them 'type' and 'code' in the absence of any other naming) I would suggest separating your criterion into two parts.  This will allow the 'type' and 'code' columns to be used as criteria ranges in a SUMIFS formula.

= LET(
    typeCriterion, TEXTBEFORE(criterion, " "),
    codeCriterion, TEXTAFTER(criterion,  " "),
    SUMIFS(values, type, typeCriterion, code, codeCriterion)
  )

@mathetes 

 

I wanted to provide the excel file initially, but I didn't know how.  I think that this link should allow anyone with it to access the file.  Please let me know if it is not accessible.

 

https://1drv.ms/x/s!AuLK-DgGW47_gf9Y5Dnzb64HDTfuYA?e=Stdtgh

I'm sorry, but I don't quite understand what you are suggesting that I do. Perhaps if you showed me in the actual excel file, it would be easier to understand.

I wanted to provide the excel file initially, but I didn't know how. I think that this link should allow anyone with it to access the file. Please let me know if it is not accessible.

https://1drv.ms/x/s!AuLK-DgGW47_gf9Y5Dnzb64HDTfuYA?e=Stdtgh

@Tamara Duvall 

=SUMIFS(E1:E11, A1:A11, "Check", C1:C11, "NATHCO")+SUMIFS(F1:F11, A1:A11, "Check", C1:C11, "NATHCO")

 

or

 

=SUMPRODUCT(E1:F11*(A1:A11="Check")*(C1:C11="NATHCO"))

@Tamara Duvall 

This assumes 365 since it uses TEXTBEFORE and TEXTAFTER to split the concatenated search string.

A separate term would be needed to pick up the final column.

PeterBartholomew_0-1703966876495.png

 

 

@Tamara Duvall 

If you absolutely need a formula, you could use SUMPRODUCT with concatenation to cut some corners.  Some may frown on concatenation in this context because it's not 'fast'.  Unless you have tens of thousands of rows, I see no issue using it:

 

=SUMPRODUCT((Item&" "&Name=input)*(Amount))

 

 

You might also consider using a table with 2 slicers. I suggest this because I suspect the 'input' might change. You may need to find the totals for something other than "Check NATHCO", for example.

 

Here's the slicer approach:

Patrick2788_1-1703967906209.png