Multuple SUMIFS conditionals?

Outdoorsman80

Board Regular
Joined
Oct 4, 2014
Messages
61
Office Version
  1. 365
Platform
  1. Windows
I'm not sure if I worded the title correctly. Yesterday you guys helped me along with a formula and it's working now. But, for another field, that I want to calculate.

In the example below, N18 on the Dash sheet, I'd l'd to have it look at the Items sheet for the term "Grainx" (I'll update that to the real term later) AND I'd like for it to search for "Yes" in the "M" column in Items as well as be in that date range as the other formulas that you see. Does that make sense? Is this even possible? I've racked my brain reading guides but this is getting to be a litte more than I can program.

Finances.xlsx
KLMN
152020
16SumTotal CostSide Money?
17Egg Sales Total29$ 114.00$ -
18Meaty Food Total3$ 74.62
19Egg Layer Grain Total12$ 282.26
20Broilers Total6$ 12.00
Dash
Cell Formulas
RangeFormula
L17L17=+SUM(SUMIFS('Sales'!B2:B5004,'Sales'!A2:A5004, {"Eggs"}, 'Sales'!E2:E5004,">="&DATE(K15,1,1), 'Sales'!E2:E5004, "<="&DATE(K15,12,31)))
M17M17=SUM(SUMIFS('Sales'!D2:D5004,'Sales'!A2:A5004, {"Eggs"}, 'Sales'!E2:E5004,">="&DATE(K15,1,1), 'Sales'!E2:E5004, "<="&DATE(K15,12,31)))
L18L18=+SUM(SUMIFS(Items!I2:I5005,Items!E2:E5005, {"Grain - Broiler Crumbles","Grain - Starter Mash"}, Items!D2:D5005,">="&DATE(K15,1,1), Items!D2:D5005, "<="&DATE(K15,12,31)))
M18M18=SUM(SUMIFS(Items!J2:J5005,Items!E2:E5005, {"Grain - Broiler Crumbles","Grain - Starter Mash"}, Items!D2:D5005,">="&DATE(K15,1,1), Items!D2:D5005, "<="&DATE(K15,12,31)))
L19L19=+SUM(SUMIFS(Items!I3:I5006,Items!E3:E5006, {"Grain"}, Items!D3:D5006,">="&DATE(K15,1,1), Items!D3:D5006, "<="&DATE(K15,12,31)))
M19M19=SUM(SUMIFS(Items!J3:J5006,Items!E3:E5006, {"Grain"}, Items!D3:D5006,">="&DATE(K15,1,1), Items!D3:D5006, "<="&DATE(K15,12,31)))
L20L20=+SUM(SUMIFS(Items!I4:I5007,Items!F4:F5007, {"Broiler"}, Items!D4:D5007,">="&DATE(K15,1,1), Items!D4:D5007, "<="&DATE(K15,12,31)))
M20M20=SUM(SUMIFS(Items!J3:J5006,Items!F3:F5006, {"Broiler"}, Items!D3:D5006,">="&DATE(K15,1,1), Items!D3:D5006, "<="&DATE(K15,12,31)))



Finances.xlsx
DEFGHIJKLM
7404/3/2021Grain50lb - Soy FreeGreen MountainN/A1$ 23.41Paris FarmsNoNo
7414/3/2021Grain50lb - Soy FreeGreen MountainN/A1$ 23.41Paris FarmsNoYes
Items
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Here is one approach that you might want to consider. Rather than embedding the search terms in the formulas, this establishes a small helper table where the main categories for tabulation are in the first row (these match the categories shown in K17:K20), and any normal match terms are in the next four rows under those categories. A fifth row in that table includes your special search term for the Side Money column (i.e., Grainx). Then the formulas that perform the count and summations are similar and rely on SUMPRODUCT.

This part creates an array of TRUE's and FALSE's where the year of the referenced date matches that in K15. Later, when we multiple this array by another array, the TRUE's and FALSE's are coerced to 1's and 0's:
Excel Formula:
(YEAR(Items!$D$2:$D$5004)=$K$15)
This part creates an array of 1's and 0's where any of the cells in Items!$E$2:$E$5004 match any of the terms taken from the appropriate column of the helper table.
Excel Formula:
COUNTIF(INDEX($R$8:$U$11,,MATCH($K18,$R$7:$U$7,0)),Items!$E$2:$E$5004)
The "appropriate" column is the one returned by the following in the above formula:
Excel Formula:
INDEX($R$8:$U$11,,MATCH($K18,$R$7:$U$7,0))
...where we use the MATCH to find the correct category heading in the helper table. So this INDEX/MATCH finds {"Grain - Broiler Crumbles";"Grain - Starter Mash";0;0}
And this term is obvious...the values column, some of which are to be summed:
Excel Formula:
(Items!$J$2:$J$5004)

The advantage with this type of approach is that the formulas don't need to be revised to search for different terms...just change the terms in the helper table. And SUMPRODUCT used like this will give a count by omitting the last term described above. When we multiply the other arrays based on logical checks and matches, we'll get an array consisting only of 1's and 0's, where the 1's represent those rows where all of the conditions are met...and SUMPRODUCT will sum those for a count. When we include the last term described above, then this same array is used to include the money values.

For the new Side Money column, I'm not quite sure what you have in mind. Do you want that to only find "Grainx" for the Meaty Food Total category? If that is so, then the adjustment shown here involves changing the INDEX/MATCH component of the helper table because we don't need the search terms shown in R8:U11. Instead we need the terms found in the extra row of that table in R12:U12...and if "Grainx" applies only to the Meaty Food Total column, then R12 is the only place where "Grainx" is added as a term to match. Additionally, we wanted to find where column M on the Items sheets is "Yes", so one extra term included in the SUMPRODUCT formula:
(Items!$M$2:$M$5004="Yes")

The result is something similar to what you had, with the helper table stashed somewhere out of the way. Some notional examples of the Items and Sales worksheets are shown to make sense of this example.
MrExcel20210428.xlsx
KLMNOPQRSTU
7CategoriesEgg Sales TotalMeaty Food TotalEgg Layer Grain TotalBroilers Total
8Match TermsEggsGrain - Broiler CrumblesGrainBroiler
9Grain - Starter Mash
10
11
12Side MoneyGrainx
13
14
152020
16CategoryCountTotal CostSide Money?
17Egg Sales Total2400
18Meaty Food Total36022.5
19Egg Layer Grain Total352
20Broilers Total117
Dash (2)
Cell Formulas
RangeFormula
L17L17=SUMPRODUCT(COUNTIF(INDEX($R$8:$U$11,,MATCH($K17,$R$7:$U$7,0)),Sales!$A$2:$A$5004)*(YEAR(Sales!$E$2:$E$5004)=$K$15))
M17M17=SUMPRODUCT((Sales!$D$2:$D$5004)*COUNTIF(INDEX($R$8:$U$11,,MATCH($K17,$R$7:$U$7,0)),Sales!$A$2:$A$5004)*(YEAR(Sales!$E$2:$E$5004)=$K$15))
L18:L20L18=SUMPRODUCT(COUNTIF(INDEX($R$8:$U$11,,MATCH($K18,$R$7:$U$7,0)),Items!$E$2:$E$5004)*(YEAR(Items!$D$2:$D$5004)=$K$15))
M18:M20M18=SUMPRODUCT((Items!$J$2:$J$5004)*COUNTIF(INDEX($R$8:$U$11,,MATCH($K18,$R$7:$U$7,0)),Items!$E$2:$E$5004)*(YEAR(Items!$D$2:$D$5004)=$K$15))
N18N18=SUMPRODUCT((Items!$J$2:$J$5004)*COUNTIF(INDEX($R$12:$U$12,,MATCH($K18,$R$7:$U$7,0)),Items!$E$2:$E$5004)*(YEAR(Items!$D$2:$D$5004)=$K$15)*(Items!$M$2:$M$5004="Yes"))


MrExcel20210428.xlsx
DEFGHIJKLM
1DateItemsYes/No
22/15/2020Grain - Broiler Crumbles10Yes
37/23/2020Grain - Starter Mash15Yes
48/5/2020B20Yes
51/16/2021Grain - Starter Mash25Yes
68/5/2020A30Yes
78/5/2020Grain - Broiler Crumbles35Yes
87/23/2020Grain23Yes
98/5/2020Grain25Yes
101/15/2020Red2Yes
111/15/2021Grain3Yes
1210/25/2020Grain4Yes
137/1/2020Broiler17Yes
143/7/2020Grainx22.5Yes
Items


MrExcel20210428.xlsx
ABCDE
1ItemAmtAmtDate
2Eggs11002/15/2020
3Eggs13007/23/2020
4Cheese17508/5/2020
5Eggs12001/16/2021
Sales
 
Upvote 0
I'm going to have to read through this a few times to digest it all. I would need to have the index table on a different sheet. I don't mind the searching formula, I basically just need to do a little bit of math and lookup automatically. :)
 
Upvote 0
Reading through it, I'm not sure that this solution will work in this situation. Are there other options? If not, I can try to work around it but just may not be able to. What you see in my formulas is as about as far as my knowledge goes.
 
Upvote 0
Could we revisit a few things?...I'm trying to understand the issues. From an earlier post, you mentioned the helper table with the search phrases would have to be on a separate sheet. That shouldn't be an issue, but how many categories do you have that would need search terms assigned to them? Your example shows four categories: Egg Sales Total, Meaty Food Total, Egg Layer Grain Total, and Broilers Total. What is the maximum number of search terms you might need for any single category. Your example had at most two, {"Grain - Broiler Crumbles","Grain - Starter Mash"}, but what is the largest number you anticipate?

For the new Side Money column, could you clarify the search criteria? I understand that you want criteria for the "same year" and for "Yes" in column M, but I still have two questions about the search terms?
1. You mentioned that you'd like to find "Grainx", but does the category matter? Are you looking for some specialty search term (like "Grainx") in any of the categories (Egg Sales Total, Meaty Food Total, Egg Layer Grain Total, and Broilers)...or does that search term apply to only some of the categories?
2. When "Grainx" is being searched for, do other search terms also apply at the same time? For example, is the Side Money formula supposed to look for {"Grain - Broiler Crumbles","Grain - Starter Mash", "Grainx"} or just {"Grainx"}?

Could you describe what you've encountered that casts doubt on whether the solution will work in your situation? For example: the solution misinterprets your requirements, the solution uses unfamiliar functions such as the SUMPRODUCT or the COUNTIF/INDEX/MATCH parts, the solution involves a helper table and there is a preference not to use one, etc.?
 
Upvote 0
Could we revisit a few things?...I'm trying to understand the issues. From an earlier post, you mentioned the helper table with the search phrases would have to be on a separate sheet. That shouldn't be an issue, but how many categories do you have that would need search terms assigned to them? Your example shows four categories: Egg Sales Total, Meaty Food Total, Egg Layer Grain Total, and Broilers Total. What is the maximum number of search terms you might need for any single category. Your example had at most two, {"Grain - Broiler Crumbles","Grain - Starter Mash"}, but what is the largest number you anticipate?

For the new Side Money column, could you clarify the search criteria? I understand that you want criteria for the "same year" and for "Yes" in column M, but I still have two questions about the search terms?
1. You mentioned that you'd like to find "Grainx", but does the category matter? Are you looking for some specialty search term (like "Grainx") in any of the categories (Egg Sales Total, Meaty Food Total, Egg Layer Grain Total, and Broilers)...or does that search term apply to only some of the categories?
2. When "Grainx" is being searched for, do other search terms also apply at the same time? For example, is the Side Money formula supposed to look for {"Grain - Broiler Crumbles","Grain - Starter Mash", "Grainx"} or just {"Grainx"}?

Could you describe what you've encountered that casts doubt on whether the solution will work in your situation? For example: the solution misinterprets your requirements, the solution uses unfamiliar functions such as the SUMPRODUCT or the COUNTIF/INDEX/MATCH parts, the solution involves a helper table and there is a preference not to use one, etc.?
Hi,

It should only apply to that one term, so specifying it is OK. 2- Yes, I can but does not have to be. I think that it would be alittle more complex for me to implement and then change down the road if I wanted to do more with it. It's difficult for me to break down this type of formula.

Basically, I just need to be able to search for a product type with side $ "yes".
 
Upvote 0
What are column B of Sales and column I of Items used for? Do they hold values of 1 for every row?...are they used only for counting?
 
Upvote 0
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
KLMN
152020
16SumTotal CostSide Money?
17Egg Sales Total2400-
18Meaty Food Total360999
19Egg Layer Grain Total35232.5
20Broilers Total11712.33
Dash
Cell Formulas
RangeFormula
L17L17=SUM(COUNTIFS(Sales!$E$2:$E$5004,">="&DATE($K$15,1,1), Sales!$E$2:$E$5004, "<="&DATE($K$15,12,31),Sales!$A$2:$A$5004, {"Eggs"}))
M17M17=SUM(SUMIFS(Sales!$D$2:$D$5004, Sales!$E$2:$E$5004,">="&DATE($K$15,1,1), Sales!$E$2:$E$5004, "<="&DATE($K$15,12,31),Sales!$A$2:$A$5004, {"Eggs"}))
L18L18=SUM(COUNTIFS(Items!$D$2:$D$5004,">="&DATE($K$15,1,1), Items!$D$2:$D$5004, "<="&DATE($K$15,12,31),Items!$E$2:$E$5004, {"Grain - Broiler Crumbles","Grain - Starter Mash"}))
M18M18=SUM(SUMIFS(Items!$J$2:$J$5004, Items!$D$2:$D$5004,">="&DATE($K$15,1,1), Items!$D$2:$D$5004, "<="&DATE($K$15,12,31),Items!$E$2:$E$5004, {"Grain - Broiler Crumbles","Grain - Starter Mash"}))
N18N18=SUM(SUMIFS(Items!$J$2:$J$5004, Items!$D$2:$D$5004,">="&DATE($K$15,1,1), Items!$D$2:$D$5004, "<="&DATE($K$15,12,31),Items!$M$2:$M$5004,"Yes",Items!$E$2:$E$5004, {"MeatyX"}))
L19L19=SUM(COUNTIFS(Items!$D$2:$D$5004,">="&DATE($K$15,1,1), Items!$D$2:$D$5004, "<="&DATE($K$15,12,31),Items!$E$2:$E$5004, {"Grain"}))
M19M19=SUM(SUMIFS(Items!$J$2:$J$5004, Items!$D$2:$D$5004,">="&DATE($K$15,1,1), Items!$D$2:$D$5004, "<="&DATE($K$15,12,31),Items!$E$2:$E$5004, {"Grain"}))
N19N19=SUM(SUMIFS(Items!$J$2:$J$5004, Items!$D$2:$D$5004,">="&DATE($K$15,1,1), Items!$D$2:$D$5004, "<="&DATE($K$15,12,31),Items!$M$2:$M$5004,"Yes",Items!$E$2:$E$5004, {"GrainX"}))
L20L20=SUM(COUNTIFS(Items!$D$2:$D$5004,">="&DATE($K$15,1,1), Items!$D$2:$D$5004, "<="&DATE($K$15,12,31),Items!$E$2:$E$5004, {"Broiler"}))
M20M20=SUM(SUMIFS(Items!$J$2:$J$5004, Items!$D$2:$D$5004,">="&DATE($K$15,1,1), Items!$D$2:$D$5004, "<="&DATE($K$15,12,31),Items!$E$2:$E$5004, {"Broiler"}))
N20N20=SUM(SUMIFS(Items!$J$2:$J$5004, Items!$D$2:$D$5004,">="&DATE($K$15,1,1), Items!$D$2:$D$5004, "<="&DATE($K$15,12,31),Items!$M$2:$M$5004,"Yes",Items!$E$2:$E$5004, {"BroilerX"}))

Here is the Items table used for this example. The Sales table is the same as shown in my post #2.
MrExcel20210428.xlsx
DEFGHIJKLM
1DateItemsAmtYes/No
22/15/2020Grain - Broiler Crumbles10Yes
37/23/2020Grain - Starter Mash15Yes
48/5/2020B20Yes
51/16/2021Grain - Starter Mash25Yes
68/5/2020A30Yes
78/5/2020Grain - Broiler Crumbles35Yes
87/23/2020Grain23Yes
98/5/2020Grain25Yes
101/15/2020Red2Yes
111/15/2021Grain3Yes
1210/25/2020Grain4Yes
137/1/2020Broiler17Yes
143/7/2020Grainx22.5Yes
157/1/2020Broilerx11.33Yes
169/3/2020Meatyx999Yes
178/22/2020Broilerx1Yes
187/30/2020Grainx10Yes
Items

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
ABCDE
1Exact expressions to search
2
3Categories -->Egg Sales TotalMeaty Food TotalEgg Layer Grain TotalBroilers Total
4Search Terms: 1EggsGrain - Broiler CrumblesGrainBroiler
52Grain - Starter Mash
63
74
8Side Money, search for -->MeatyxGrainxBroilerx
SearchList

And the Dash sheet (here I've called it Dash (2)) would look like this:
MrExcel20210428.xlsx
KLMN
152020
16CategoryCountTotal CostSide Money?
17Egg Sales Total2400-
18Meaty Food Total360999
19Egg Layer Grain Total35232.5
20Broilers Total11712.33
Dash (2)
Cell Formulas
RangeFormula
L17L17=SUMPRODUCT(COUNTIF(INDEX(SearchList!$B$4:$E$7,,MATCH($K17,SearchList!$B$3:$E$3,0)),Sales!$A$2:$A$5004), --(YEAR(Sales!$E$2:$E$5004)=$K$15))
M17M17=SUMPRODUCT(Sales!$D$2:$D$5004, COUNTIF(INDEX(SearchList!$B$4:$E$7,,MATCH($K17,SearchList!$B$3:$E$3,0)),Sales!$A$2:$A$5004), --(YEAR(Sales!$E$2:$E$5004)=$K$15))
L18:L20L18=SUMPRODUCT(COUNTIF(INDEX(SearchList!$B$4:$E$7,,MATCH($K18,SearchList!$B$3:$E$3,0)),Items!$E$2:$E$5004), --(YEAR(Items!$D$2:$D$5004)=$K$15))
M18:M20M18=SUMPRODUCT(Items!$J$2:$J$5004, COUNTIF(INDEX(SearchList!$B$4:$E$7,,MATCH($K18,SearchList!$B$3:$E$3,0)),Items!$E$2:$E$5004), --(YEAR(Items!$D$2:$D$5004)=$K$15))
N18:N20N18=SUMPRODUCT(Items!$J$2:$J$5004, COUNTIF(INDEX(SearchList!$B$8:$E$8,,MATCH($K18,SearchList!$B$3:$E$3,0)),Items!$E$2:$E$5004), --(YEAR(Items!$D$2:$D$5004)=$K$15), --(Items!$M$2:$M$5004="Yes"))

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).
 
Upvote 0
Solution
What are column B of Sales and column I of Items used for? Do they hold values of 1 for every row?...are they used only for counting?
Column B is just a totals column, x amount of product sold. I is amount as well for a product purchased. X amount of grainx, etc.

Thank you for your help and time explaining, this is helpful.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top