# SUMIF or SUMPRODUCT with more than one condition

I need to add one more condition to this SUMIF formula: SUMIF(AllTails,C6,AllJ)

I need to sum all the cells in the defined range "AllJ" for which its corresponding cell in the range "AllTails" = C6   AND   its corresponding cell the range "AllWeeks" = "Week 40"

Thanks,
John
LVL 1
###### Who is Participating?

Commented:
John,
You need to use sumproduct that is this formula.
=sumproduct((alltails=C6)*(ALLWEEKS="Week 40")*ALLJ)
Saurabh
0

Reliability Business Tools Analyst IIAuthor Commented:
His saurabh, I tried it but I get an #N/A error (??). Can you see anything that might be off?
Also, it would be okay to accomplish this with a macro if that helps.
Thanks
0

Data EngineerCommented:
0

Commented:
It can give you NA Error primarliy because of the reason that all your ranges in the criteria are not same what i mean by that if all tails is assuming-->a1 to a100, then all weeks should also from row-1 to row-100 that is assuming its b column it will be b1 to b100 and similarly all j should also be from row-1 to row 100 that is assuming c column then-->c1 to c100, check your ranges im sure they will be inconsistent.
If they are same then somewhere in your data you have a error value #NA.
Saurabh...
0

Reliability Business Tools Analyst IIAuthor Commented:
It took me a while to scrub it, but here it is.
Thanks,
John

SUMIF-Challenge.xls
0

Commented:
As stated my earlier comment, your range all tails was dynamic as soon i make it static and apply formula it perfectly works. Enclosed is your file.
Saurabh...

SUMIF-Challenge.xls
0

Reliability Business Tools Analyst IIAuthor Commented:
Thanks, saurabh, it works great. Sorry for the wild goose chase!

- John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.