Problem using SUBSTITUTE to create CRITERIA for SUMIFS range/criteria pair

[Bcarey]

I am trying to use SUBSTITUTE to create a criteria for one of range/Criteria of a SUMIFS function.
The function I am trying to get to work is a SUMIIF with 4 range/criteria pairs

• 1 - Is the row date is equal or greater than the C$1 date value
• 2 - Is the row date is equal or less than the C$1 date value + 6 (Range/Criteria sets one and two are to insure only including data from one week)
• 3 - Is the row animal type equal to the $A2 value (only include data for the animal type indicated in column A for this row)
• 4 - Is the row color match one of the values in $B2 data (only include data for the color types indicated in column B for this row)

The function has no errors but does not match anything and returns a zero.

The SUBSTITUE is taking the cell with the color type and converting it to an OR structure for the forth criteria.
For example cell B2 “Brown, Grey” would become after the nested SUBSTITUTE
{“*Brown*”,”*Grey*”}



If I remove the nested SUBSTITUTE and put {“*Brown*”,”*Grey*”} in directly it works.



**What do I need to do to get the SUMIFS to recognize the output from my nested SUBSTITUTE



NOTE: the substitute is adding the open and close brackets removing the spaces and adding the asterisks and double quotes.



Below is the full function and the spread sheets I am trying to get to work:

=SUM(
SUMIFS(
Sheet2!$D$2:$D$24,
Sheet2!$A$2:$A$24,">="&C$1,
Sheet2!$A$2:$A$24,"<="&C$1+6,
Sheet2!$B$2:$B$24,"="&$A2,
Sheet2!$C$2:$C$24,SUBSTITUTE(
SUBSTITUTE(
"{""*"&$B2&"*""}",
",",
"*"", ""*"
),
" ",
""
)
)
)




Here are the example Sheets I put together that demonstrates this issue

Sheet1:

Animal	Color Type	3/7/22​	3/14/22​	3/21/22​
DOG	Brown, Grey	0​	0​	0​
Cat	Mix, Black	0​	0​	0​
Horse	Black, White, Brown	0​	0​	0​
Bird	Grey, White	0​	0​	0​

Sheet2:

Date	animal	color type	count
3/7/2022​	Dog	Brown	3​
3/7/2022​	Dog	White	4​
3/7/2022​	Dog	Black	3​
3/7/2022​	Dog	Grey	4​
3/7/2022​	Cat	Mix	8​
3/7/2022​	Horse	Brown	5​
3/7/2022​	Horse	White	4​
3/7/2022​	Bird	Black	7​
3/7/2022​	Bird	Grey	9​
3/14/2022​	Bird	Mix	8​
3/14/2022​	Bird	white	6​
3/14/2022​	Dog	White	7​
3/14/2022​	Dog	Grey	2​
3/14/2022​	Cat	White	3​
3/14/2022​	Horse	Brown	3​
3/14/2022​	Horse	Black	8​
3/21/2022​	Dog	Grey	3​
3/21/2022​	Dog	Mix	5​
3/21/2022​	Cat	Brown	5​
3/21/2022​	Cat	White	9​
3/21/2022​	Cat	Black	2​
3/21/2022​	Cat	Grey	6​
3/21/2022​	Horse	Mix	2​

 

[Bcarey]

The function was suppose to look like the following, posted it by accident before finishing:
=SUM(
SUMIFS(
Sheet2!$D$2:$D$24,
Sheet2!$A$2:$A$24,">="&C$1,
Sheet2!$A$2:$A$24,"<="&C$1+6,
Sheet2!$B$2:$B$24,"="&$A2,
Sheet2!$C$2:$C$24,SUBSTITUTE(
SUBSTITUTE(
"{""*"&$B2&"*""}",
",",
"*"", ""*"
),
" ",
""
)
)
)

 

[Fluff]

You cannot do that, as the substitute will return a string & not an array, you could use filterxml instead.

=SUM(SUMIFS(Sheet2!$D$2:$D$24,Sheet2!$A$2:$A$24,">="&C$1,Sheet2!$A$2:$A$24,"<="&C$1+6,Sheet2!$B$2:$B$24,"="&$A2,Sheet2!$C$2:$C$24,FILTERXML("<k><m>"&SUBSTITUTE($B2,",","</m><m>")&"</m></k>","//m")))

 

[Bcarey]

Thank you!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Bcarey]

How would I use the FILTERXML is I was looking to for cells that did not contain the colors in sheet 1.
For example looking for dogs that are not "Brown, Grey"

I tried the FILTERXML with a "<>" and it added the cells twice once to Brown not in the cell and once for Grey not in the cell,
and I only once when both are not in the cell.

 

[Fluff]

You can't that construct is for OR criteria, whereas you need to use AND.
You would need to use something like

=SUM(SUMIFS(Sheet2!$D$2:$D$24,Sheet2!$A$2:$A$24,">="&C$1,Sheet2!$A$2:$A$24,"<="&C$1+6,Sheet2!$B$2:$B$24,$A2,Sheet2!$C$2:$C$24,"<>"&LEFT(B2,FIND(",",B2)-1),Sheet2!$C$2:$C$24,"<>"&MID(B2,FIND(",",B2)+2,100)))

 

[Bcarey]

that would be fine if I had a set number, but in my actual sheet I can have from 1 to 5 items to compare at any time

 

[Fluff]

In that cas as it's a totally different question, you will need to start a new thread if you need further help.