Sum(Sumif Counting a cell twice problem

[jwillits]

Not sure what I am doing wrong, here is my formula

=SUM(SUMIFS($D:$D,$J:$J,"Apr 2021",$C:$C,{"*AMAZON*","*AMZN*"}))

What I am trying to do is cell D if J="Apr 2021", and if C contains AMAZON or AMZN, however, it is counting the D cell twice because it contains both AMAZON and AMZN... how do I only count it once?

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post a small sample of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jwillits]

I am using Excel 2010
___________B_______________________________________________C______________________________________________________________________________D_______E________F__________ G_________H________I______________J

4/19/2021​

Amazon.com*6H8DV8FX3 Amzn.com/bill WA 04/18

-116.88​

FALSE​

TRUE​

Apr

2021

Apr 2021

Here is a sample of my data, and I want to calculate the 116.88, but with this formula,

=SUM(SUMIFS($D:$D,$J:$J,"Apr 2021",$C:$C,{"*AMAZON*","*AMZN*"}))

I am getting 233.76

 

[Fluff]

Thanks for that.
How about

=SUMPRODUCT(($J$2:$J$100="Apr 2021")*(((ISNUMBER(SEARCH("amazon",$C$2:$C$100)))+(ISNUMBER(SEARCH("amzn",$C$2:$C$100))))>0)*($D$2:$D$100))

 

[jwillits]

Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.