Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 640
  • Last Modified:

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
0
gabrielPennyback
Asked:
gabrielPennyback
  • 3
  • 3
1 Solution
 
Saurabh Singh TeotiaCommented:
John,
You need to use sumproduct that is this formula.
=sumproduct((alltails=C6)*(ALLWEEKS="Week 40")*ALLJ)
Saurabh
0
 
gabrielPennybackAuthor 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
 
SharathData EngineerCommented:
Can you attach your spreadsheet here?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Saurabh Singh TeotiaCommented:
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
 
gabrielPennybackAuthor Commented:
It took me a while to scrub it, but here it is.
Thanks,
John

SUMIF-Challenge.xls
0
 
Saurabh Singh TeotiaCommented:
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
 
gabrielPennybackAuthor Commented:
Thanks, saurabh, it works great. Sorry for the wild goose chase!

- John
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now