SUMIFS with Multiple Criteria and a Partial match

1. SUMIFS with Multiple Criteria and a Partial match

Can you use sumifs with multiple criteria and a partial match?

SUMIFS(\$E2:\$E\$292,\$A\$2:\$A\$292,\$T\$3,\$D\$2:\$D\$292,\$H6,\$A\$2:\$A\$292,"*"&\$T\$6&"*")

I am get a value of zero when I should be getting a sum.

2. Re: SUMIFS with Multiple Criteria and a Partial match

Yes, you can use wildcards like that.
The syntax of your formula appears fine.

Could be many reasons for it to result in z
Can you post a sample book?

Could be the values in column E are not really numbers, but "numbers stored as text"
Could be none of the rows meet ALL 3 Criteria

3. Re: SUMIFS with Multiple Criteria and a Partial match

THe formula works until I add the last criteria for the partial match.

\$A\$2:\$A\$292,"*"&\$T\$6&"*"

4. Re: SUMIFS with Multiple Criteria and a Partial match

OK, what's in Column A, and what's in T6 ?

5. Re: SUMIFS with Multiple Criteria and a Partial match

I think..

\$A\$2:\$A\$292,\$T\$3, \$A\$2:\$A\$292, "SomethingElse"

will never meet.. as you are checking both.. in same range..

something like..
{=Sum(sumif(\$A\$2:\$A\$292,{"\$T\$3","SomethingElse"}))}

6. Re: SUMIFS with Multiple Criteria and a Partial match

Column A is my part numbers (ex 114234L32,114234L33) , T6 is the partial part number 114234L.

7. Re: SUMIFS with Multiple Criteria and a Partial match

Originally Posted by Debraj Roy
I think..

\$A\$2:\$A\$292,\$T\$3, \$A\$2:\$A\$292, "SomethingElse"

will never meet.. as you are checking both.. in same range..
That's a good catch, I didn't see that.

So what's in T3 then ?

Perhaps then just

SUMIFS(\$E2:\$E\$292,\$D\$2:\$D\$292,\$H6,\$A\$2:\$A\$292,"*"&\$T\$6&"*")

8. Re: SUMIFS with Multiple Criteria and a Partial match

Another part number.

9. Re: SUMIFS with Multiple Criteria and a Partial match

so you cant use a column twice in your criteria range when using SUMIFS?

10. Re: SUMIFS with Multiple Criteria and a Partial match

OK, so as Roy said, column A can't be equal to BOTH T3 AND *T6*...
Unless T3 happens to contain T6.

Are you trying to do an OR criteria?
If A = T3 OR A = *T6*

11. Re: SUMIFS with Multiple Criteria and a Partial match

no, trying sum parts numbers available per state. I have many same multiple parts with different revs, hence why am trying to use a partial match (T6)

12. Re: SUMIFS with Multiple Criteria and a Partial match

So why are you checking column A for 2 different criteria?

What is in T3 and T6..
Don't say part numbers...
Actually copy the contents from the cells and paste them into the thread.

Or better yet, post a sample workbook.

13. Re: SUMIFS with Multiple Criteria and a Partial match

11923L200
11923L201
11823L300

Multiple part numbers for the same part 11823L300 (T3), 11923L is the partial

14. Re: SUMIFS with Multiple Criteria and a Partial match

Try the formula I posted back in post #7
Originally Posted by Jonmo1
Perhaps then just

SUMIFS(\$E2:\$E\$292,\$D\$2:\$D\$292,\$H6,\$A\$2:\$A\$292,"*"&\$T\$6&"*")

15. Re: SUMIFS with Multiple Criteria and a Partial match

Thank you. It worked this time.

16. Re: SUMIFS with Multiple Criteria and a Partial match

You're welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1