Apr 18 2020 08:46 AM
This worksheet (produced in Office 365) is part of a larger workbook that I am putting together to produce monthly financials. There is a worksheet named transactions_data which is being searched to sum amounts for each vendor in column A, that is within a certain date range $D$2 to $E$2 (these are hidden in the attached PDF), and that has text within the string of another column of transactions_data that contains "expense" or "COGS".
This is the formula that I entered from B7 to B95:
=SUMIFS(transactions_data!$G:$G,transactions_data!$J:$J,$A7,transactions_data!$B:$B,">="&$D$2,transactions_data!$B:$B,"<="&$E$2,transactions_data!$C:$C,{"*Expense*","*COGS*"})
There are a few minor errors where the correct total was not picked up that I will have to research, but there is one odd error that I have never seen before, and even after looking at information on the internet, am unable to fix. Many of the values are calculated correctly, but are "split". I assume that the amounts that are displayed in column B are ones that match "*Expense*", and the ones in column C are the ones that match "*COGS*". What I can't figure out is how to make the amounts display in one column as desired (column B).
Any help would be appreciated.
Apr 18 2020 09:07 AM
SolutionThat's since you have an array as latest condition. If you'd like to have OR condition, wrap entire formula with sum, i.e. =SUM(SUMIFS(...))
Apr 18 2020 09:15 AM
Thank you so much! The =SUM option worked beautifully.
Apr 18 2020 09:07 AM
SolutionThat's since you have an array as latest condition. If you'd like to have OR condition, wrap entire formula with sum, i.e. =SUM(SUMIFS(...))