SUMIF & SUMIFS Followup In #Excel

Here is a follow up post based on a question I received on my previous post about SUMIF & SUMIFS:

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

I was asked a question from my post yesterday on SUMIF and SUMIFS if it is possible to have the criteria be the length of the name of the salesman. For example, total up the sales if the length of the name is greater than 3 characters.

I wasn’t able to come up with a solution that incorporates the LEN function directly into the SUMIF or SUMIFS function, but did arrive at two alternatives:

1) add a helper column
2) use SUMPRODUCT instead

Here I’ve added a helper column that incorporates the formula to determine the length of the salesman’s name:

sp1

Now I can just use the SUMIFS to test off that column for length >3. That formula is:

=SUMIFS(SUMIFTable[Sales],SUMIFTable[Column1],”>3″)

sp2

The other approach is the use the SUMPRODUCT function. If you have not familiar with that function, please refer to several posts that I’ve done in the past:

Here are links to previous posts on SUMPRODUCT:
sumproduct instead of sumif
more uses of sumproduct
vlookup with sumproduct plus if and isnumber
vlookup with sumproduct or sumifs
sumproduct two steps in one

That formula is:

=SUMPRODUCT((SUMIFTable[Sales])*(LEN(SUMIFTable[Salesman])>3))

sp3

Both can work in our scenario and give us the correct answer.

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , ,

No comments yet.

Leave a Reply