If you want to keep formulas similar to what you first posted, then here is a version that maintains the embedded search strings. It assumes that you are using column B of Sales and column I of Items as counters (i.e., those columns contain only 1's on each row). And if that is true, then this version replaces the SUM(SUMIFS construction with a SUM(COUNTIFS construction to shorten the formula a little bit. Note that I've rearranged the terms within the COUNTIFS and SUMIFS to move the array of search terms to the end of the formula where they are easier to find and edit as needed. The Side Money formula includes the same date criteria as the other columns, but it eliminates all search terms except for the one appearing at the end of the formula, where I hardwired in "MeatyX", "GrainX", and "BroilerX". Just edit those terms to any category-specific search term desired. The Side Money formula also includes this term...Items!$M$2:$M$5004,"Yes"...which requires that column M contain a "Yes" for the row to be included in any count or sum.
MrExcel20210428.xlsx |
---|
|
---|
| K | L | M | N |
---|
15 | 2020 | |
---|
16 | | Sum | Total Cost | Side Money? |
---|
17 | Egg Sales Total | 2 | 400 | - |
---|
18 | Meaty Food Total | 3 | 60 | 999 |
---|
19 | Egg Layer Grain Total | 3 | 52 | 32.5 |
---|
20 | Broilers Total | 1 | 17 | 12.33 |
---|
|
---|
Here is the Items table used for this example. The Sales table is the same as shown in my post #2.
MrExcel20210428.xlsx |
---|
|
---|
| D | E | F | G | H | I | J | K | L | M |
---|
1 | Date | Items | | | | | Amt | | | Yes/No |
---|
2 | 2/15/2020 | Grain - Broiler Crumbles | | | | | 10 | | | Yes |
---|
3 | 7/23/2020 | Grain - Starter Mash | | | | | 15 | | | Yes |
---|
4 | 8/5/2020 | B | | | | | 20 | | | Yes |
---|
5 | 1/16/2021 | Grain - Starter Mash | | | | | 25 | | | Yes |
---|
6 | 8/5/2020 | A | | | | | 30 | | | Yes |
---|
7 | 8/5/2020 | Grain - Broiler Crumbles | | | | | 35 | | | Yes |
---|
8 | 7/23/2020 | Grain | | | | | 23 | | | Yes |
---|
9 | 8/5/2020 | Grain | | | | | 25 | | | Yes |
---|
10 | 1/15/2020 | Red | | | | | 2 | | | Yes |
---|
11 | 1/15/2021 | Grain | | | | | 3 | | | Yes |
---|
12 | 10/25/2020 | Grain | | | | | 4 | | | Yes |
---|
13 | 7/1/2020 | Broiler | | | | | 17 | | | Yes |
---|
14 | 3/7/2020 | Grainx | | | | | 22.5 | | | Yes |
---|
15 | 7/1/2020 | Broilerx | | | | | 11.33 | | | Yes |
---|
16 | 9/3/2020 | Meatyx | | | | | 999 | | | Yes |
---|
17 | 8/22/2020 | Broilerx | | | | | 1 | | | Yes |
---|
18 | 7/30/2020 | Grainx | | | | | 10 | | | Yes |
---|
|
---|
I would still recommend a helper table in this application so that you can more easily see and edit the search terms in only one place---in the helper table rather than in each individual formula. In practice, this table could reside on a separate sheet like this (named "SearchList"):
MrExcel20210428.xlsx |
---|
|
---|
| A | B | C | D | E |
---|
1 | Exact expressions to search | | | | |
---|
2 | | | | | |
---|
3 | Categories --> | Egg Sales Total | Meaty Food Total | Egg Layer Grain Total | Broilers Total |
---|
4 | Search Terms: 1 | Eggs | Grain - Broiler Crumbles | Grain | Broiler |
---|
5 | 2 | | Grain - Starter Mash | | |
---|
6 | 3 | | | | |
---|
7 | 4 | | | | |
---|
8 | Side Money, search for --> | | Meatyx | Grainx | Broilerx |
---|
|
---|
And the Dash sheet (here I've called it Dash (2)) would look like this:
MrExcel20210428.xlsx |
---|
|
---|
| K | L | M | N |
---|
15 | 2020 | |
---|
16 | Category | Count | Total Cost | Side Money? |
---|
17 | Egg Sales Total | 2 | 400 | - |
---|
18 | Meaty Food Total | 3 | 60 | 999 |
---|
19 | Egg Layer Grain Total | 3 | 52 | 32.5 |
---|
20 | Broilers Total | 1 | 17 | 12.33 |
---|
|
---|
To make it work correctly, you would have to ensure that the text in the yellow cells is the same because those row and column headings are relied upon by the MATCH function. The trickiest part of the formulas used in this approach is the one responsible for determining which rows in the Sales and Items worksheets contain the search terms that are relevant to the category. That part of the formula (taken from the formula in cell M18) is:
Excel Formula:
COUNTIF(INDEX(SearchList!$B$4:$E$7,,MATCH($K18,SearchList!$B$3:$E$3,0)),Items!$E$2:$E$5004)
When broken down into components, it is fairly straightforward:
Starting inside, MATCH($K18,SearchList!$B$3:$E$3,0) takes the text in $K18 ("Meaty Food Total") and looks for an exact match (the last "0" parameter) in the column headings described by the range SearchList!$B$3:$E$3. It finds a match in the 2nd column of that range (i.e., in row 3, of columns B, C, D, and E, it finds "Meaty Food Total" in the "C" column) and returns a value of 2. So
MATCH($K18,SearchList!$B$3:$E$3,0) evaluates to 2, and that 2 is used in the INDEX function:
Excel Formula:
INDEX(SearchList!$B$4:$E$7,,MATCH($K18,SearchList!$B$3:$E$3,0))
which, after substituting in the "2" just discussed, becomes
Excel Formula:
INDEX(SearchList!$B$4:$E$7, ,2)
The INDEX function takes the form INDEX(range, row, column) and is used to describe a range of cells, or just one cell, depending on what the row and column terms are. In this case, INDEX says to look at the entire helper table of search terms, SearchList!$B$4:$E$7 and take the 2nd column. Note that nothing is shown for the row parameter...it was left blank and you will see a double set of commas. The missing row parameter means to take all rows in the range. So INDEX(SearchList!$B$4:$E$7, ,2) returns {"Grain - Broiler Crumbles";"Grain - Starter Mash";0;0}, which is what you'll see if you read down the list of search terms under Meaty Food Total (note that blanks are treated as 0). Finally this array of search terms is fed into the COUNTIF formula, so:
Excel Formula:
COUNTIF(INDEX(SearchList!$B$4:$E$7,,MATCH($K18,SearchList!$B$3:$E$3,0)),Items!$E$2:$E$5004)
which, after substituting in the search terms array, becomes
Excel Formula:
COUNTIF( {"Grain - Broiler Crumbles";"Grain - Starter Mash";0;0}, Items!$E$2:$E$5004 )
This function cycles through Items!$E$2:$E$5004 and generates a 0 or 1 for each array position, depending on whether any of the non-zero search search terms are found at that position. This array is operated on by the SUMPRODUCT function to account for other conditions (matching year, "Yes" in column M if applicable) to determine which rows match all criteria. And SUMPRODUCT will sum those rows to perform a count, or if SUMPRODUCT incorporates the cost column range, then a sum of the relevant costs will be returned.
In practice, you may never have to edit this part of the formula unless the number of categories changes (expand the number of columns in the helper table) or if you need more than four search terms for a category (expand the number of rows in the helper table).