Forum Discussion
Need help with a formula to sum of the values in column E and column F in every row (1 thru 11) if t
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)
)- 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=StdtghIf 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:
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.
