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:
Now I can just use the SUMIFS to test off that column for length >3. That formula is:
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:
Both can work in our scenario and give us the correct answer.