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

[Tamara Duvall]

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

[#2]

@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.

[#3]

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

[#4]

@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

[#5]

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

[#6]

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

[#7]

@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.

[#8]

@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: