asked on

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

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

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

Sharath S

Can you attach your spreadsheet here?

Saurabh Singh Teotia

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...

John Carney

ASKER

It took me a while to scrub it, but here it is.
Thanks,
John

SUMIF-Challenge.xls

Saurabh Singh Teotia

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

John Carney

ASKER

Thanks, saurabh, it works great. Sorry for the wild goose chase!

- John