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

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

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.

 A B C D E F 1 Check NATHCO 20221025 Vandenboom, Gene Deck Repair - Harris Ct 1,098.35 - 2 Check NATHCO 20221109 Carlson, Ashley Exterior Door 522.22 - 3 Venmo 20221206 Drummond, Scott Basement Framing 1,300.04 - 4 Bus Check 20230101 DeVuyst, Dick Garage 16120 Kenneth Rd 3,706.08 - 5 Check BAO 20230119 Mayhew, Barry Fence Repair 1,560.60 - 6 Check 20230301 Pattin, Ann Shutters (24.31) - 7 PayPal 20230301 Pattin, Ann General Interior I 2,099.30 - 8 Bus Check 20230321 Lane, Jeremy Homesteader Cafe Drywall & Trim 479.37 - 9 Check NATHCO 20230322 Hopkins, Ruth Exterior Painting 7,554.58 - 10 Venmo 20230322 Griffin, Cecily Exterior Door 735.90 - 11 Bad 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

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

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.

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

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)
)``````

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

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

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

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

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

=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"))

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

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.

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

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: