# There has to be a easier way

#### Karl E

##### New Member
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

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
Try...
Rich (BB code):
``````
=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

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

Try...
Rich (BB code):
``````
=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

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

Try...
Rich (BB code):
``````
=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

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.

Replies
1
Views
597
Replies
5
Views
365
Replies
25
Views
2K
Replies
1
Views
542
Replies
1
Views
230

1,196,255
Messages
6,014,283
Members
441,812
Latest member
Oseitutuakrasi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back