There has to be a easier way

[Karl E]

Working in windows 7 on excel 2015. I'm using a formula to track the number of times 7 pamphlets (all related to nutrition) show up in a database tracking library use over the course of the year. There are several other categories of pamphlets I need to be able to track but so far the only formula I've been able to create that works is this:

=SUMIFS(Usage!$M$1:$M$14284,<wbr>Usage!$C$1:$C$14284,"=clinic staff",Usage!$F$1:$F$14284,"=<wbr>pamphlet",Usage!$L$1:$L$14284,<wbr>"**Healthy Diet is for Everyone**")+SUMIFS(Usage!$M$<wbr>1:$M$14284,Usage!$C$1:$C$<wbr>14284,"=clinic staff",Usage!$F$1:$F$14284,"=<wbr>pamphlet",Usage!$L$1:$L$14284,<wbr>"**About Good Nutrition**")+SUMIFS(Usage!$M$<wbr>1:$M$14284,Usage!$C$1:$C$<wbr>14284,"=clinic staff",Usage!$F$1:$F$14284,"=<wbr>pamphlet",Usage!$L$1:$L$14284,<wbr>"**What You Should Know About Sodium in Your Diet**")+SUMIFS(Usage!$M$1:$M$<wbr>14284,Usage!$C$1:$C$14284,"=<wbr>clinic staff",Usage!$F$1:$F$14284,"=<wbr>pamphlet",Usage!$L$1:$L$14284,<wbr>"**Low-Sodium Eating: Tips for Kicking the Salt Habit**")+SUMIFS(Usage!$M$1:$<wbr>M$14284,Usage!$C$1:$C$14284,"=<wbr>clinic staff",Usage!$F$1:$F$14284,"=<wbr>pamphlet",Usage!$L$1:$L$14284,<wbr>"**What You Should Know About Good Nutrition in Later Years**")+SUMIFS(Usage!$M$1:$<wbr>M$14284,Usage!$C$1:$C$14284,"=<wbr>clinic staff",Usage!$F$1:$F$14284,"=<wbr>pamphlet",Usage!$L$1:$L$14284,<wbr>"**What You Should Know About Fat in Your Diet**")+SUMIFS(Usage!$M$1:$M$<wbr>14284,Usage!$C$1:$C$14284,"=<wbr>clinic staff",Usage!$F$1:$F$14284,"=<wbr>pamphlet",Usage!$L$1:$L$14284,<wbr>"**About Fiber in Your Diet**")+SUMIFS(Usage!$M$1:$M$<wbr>14284,Usage!$C$1:$C$14284,"=<wbr>clinic staff",Usage!$F$1:$F$14284,"=<wbr>pamphlet",Usage!$L$1:$L$14284,<wbr>"**You and Your Weight**")

I tried creating a formula using a named range but the formula failed. I also tried creating a table to reference the titles to tally but that formula failed as well. I'm desperate for help. Surely there's an easier way to accomplish this task. Any help would be much appreciated. I truly hope I'm describing this issue clearly because I'm certain there's someone out there who can help. Thank you in advance to anyone who responds.

Karl E

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(SUMIFS(Usage!M:M,Usage!C:C,"=clinic staff",
    Usage!F:F,"=pamphlet",Usage!L:L,List))

List is the name of a range housing the following five criteria:

Low-Sodium Eating: Tips for Kicking the Salt Habit
What You Should Know About Good Nutrition in Later Years
What You Should Know About Fat in Your Diet
About Fiber in Your Diet
You and Your Weight

If needed, put a star arounf each criterion in the list.

 

[Karl E]

Try...

=SUMPRODUCT(SUMIFS(Usage!M:M,Usage!C:C,"=clinic staff",
    Usage!F:F,"=pamphlet",Usage!L:L,List))

List is the name of a range housing the following five criteria:

Low-Sodium Eating: Tips for Kicking the Salt Habit
What You Should Know About Good Nutrition in Later Years
What You Should Know About Fat in Your Diet
About Fiber in Your Diet
You and Your Weight

If needed, put a star arounf each criterion in the list.[/QUOT
Oh my gosh; It Works!!! Thank You Thank You!! I've tried to use SumProduct before but couldnt get it formatted correctly
Thank you so much. I've worked on this for quite a long time but apparently I dont understand the process behind SumProduct very well. Many Many Many Thanks!!!!!!! Mr. Akyurek; You Rock!!

Karl E

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(SUMIFS(Usage!M:M,Usage!C:C,"=clinic staff",
    Usage!F:F,"=pamphlet",Usage!L:L,List))

List is the name of a range housing the following five criteria:

Low-Sodium Eating: Tips for Kicking the Salt Habit
What You Should Know About Good Nutrition in Later Years
What You Should Know About Fat in Your Diet
About Fiber in Your Diet
You and Your Weight

If needed, put a star arounf each criterion in the list.

Oh my gosh; It Works!!! Thank You Thank You!! I've tried to use SumProduct before but couldnt get it formatted correctly
Thank you so much. I've worked on this for quite a long time but apparently I dont understand the process behind SumProduct very well. Many Many Many Thanks!!!!!!! Mr. Akyurek; You Rock!!

Karl E

You are welcome. The real work however, i.e., the conditional summing, is still done by SUMIFS that calculates a sum for each criterion in List. This gives something like

{10;25,32.5;...}

The surrounding SUMPRODUCT just sums the foregoing work of SUMIFS.