Excel Magic Trick 992: Partial Text Criteria for a Number, SUMIF Won't Work

See how to add Numbers where ID has last three digits equal to 123:
1. Learn how wildcard for SUMIF will not work with numbers.
2. Resultant Array from array formula element can not be used in SUMIF function
3. Create array formula with the functions: RIGHT and SUMPRODUCT.
4. Learn about data mismatch between numbers as text and numbers. See how to use ampersand (join symbol) and null text string to convert number to number as text.
5. Convert TRUEs and FALSEs (logical values or Boolean values) to ones and zeroes (1s and 0s) using double negative.

[hanif_a]

I thought I knew a lot about Excel (as I could just about guess every method you've used in a lot of your videos) - but this one video has just blown my mind.

[nimrodts]

Hello Mike.
That was a nice trick with the sumproduct. I love it!
An alternative to using the Right function is to use the Mod function like this:
=SUMPRODUCT(--(MOD(B2:B10, 1000)=D2), A2:A10)
It has the advantage of not needing to convert the "123" (cell D2) into text.

[loraliu]

I tried this formula by replacing the RIGHT with the LEFT, it is really useful formula. Thanks!

[excelisfun]

You are welcome! Thanks for watching at a world record level!

[ExcelStrategy]

This is the first time that I see an array used inside the RIGHT function !

[excelisfun]

Very nice! 3 array calculations instead of 4!

[excelisfun]

Cool! Thanks for still watching these videos, even though you are an Excel Master, Hanif A!!!!

[excelisfun]

Not currently, work and family have stopped me dead in my tracks for a while. I have soooo many projects backed up that it is hard to get to my true way of having fun: excelsifun... :(

[krn]

Excellent, thanks for another great video Mike.

[VITORB]

How do you remember these formulas. I've up so much time in Access, i must practice more my Excel.

[excelisfun]

Yes. I do not have a video on this. Post question here:

mrexcel [dot] com/forum

[okeeheeleebaseballassociat]

So i am having trouble with a workbook. wondering how to get help on it?

[GTRN]

What a boss! I thought my excel was broken as I couldnt get the wild cards to work with numbers, even did a restart, I didn't give up and found you video via google!. how would you write the formula to match this number 100?12345 where 4th digit is wild card?

[MrSarky]

Hey Mike,

What came to mind, basically I think the same thing as you did:
=SUMPRODUCT(--(RIGHT(B2:B10, 3)*1=$D$2), A2:A10)

Any disadvatages of this one?

[ronwb]

Not sure of the double negative here.

[jasondejager]

Hi


How could i get this to work in a filtered list. If i change my filter list it is calculating the visible and invisible cells?
=SUM(SUMPRODUCT(--(LEFT(To, 4)=2787&""), Cost))


Regards