Summing pivot fields where value is 1

I have a report series for monthly injuries reported at all my company's locations. I filter by location and display the injury types and the number of each, as per the sample workbook attached. From this, a pie chart is built for each location.

The problem is that there are always so many one-shot injury types (we encourage reporting of every single incident, no matter how minor) that an automatic pie chart invariably includes so many tiny slivers that the chart is impossible to read. I've been manually summing the single incidents, copying it and the rest of the injury data to another location in the worksheet, and building the pie chart from that.

When these reports were done on an ad-hoc, by-request basis, I could afford the time to monkey with the single incidents, but now I have to make the reports monthly, and there are nearly 70 locations involved. Is there a way to make injury types of "1" sum up into a "Miscellaneous" field automatically within the pivot table? (I'm using Excel 2010.)

Thanks very much for the reply, judgeh59!

However, I'm concerned about the changing nature of the data from month to month. Not all the same injuries will occur each month, and they will occur in various numbers. How do I ensure that only the single injuries get into that grouping each month?

But doesn't this mean that I have to go in and perform this processing each month? If so, I'm not sure that's going to help me very much, as it would be almost as time-consuming to do that for 70 locations as it is to manually sum the "misc"'s. Or perhaps I am not understanding the process correctly?

Yes, it makes sense. Using a COUNTIF was something I was considering, but I couldn't figure out how to implement it properly. If you know how it should be done, I could use some guidance.

Thanks! I was able to create a dynamic named range and the COUNTIF formula works for totaling up the single miscellaneous incidents.

Now what I'm trying to figure out is how to get all the information lined up properly for a pie chart to display it. What's the best way to build a table that displays the injury types and numbers for those injuries that are greater than 1, and then just the name "Misc Single Incidents" and the sum of the all the rest?

My original upload was simply a pivot table that filtered injury data by location and dumped the injury types and their totals. My original question was about how to force the summing of all the one-shot injuries into a single "miscellaneous category," which we've accomplished.

However, the ultimate goal is to create an automatically-generated display of this data that will yield a usable pie chart. As it stands, I haven't figured out a way to get that clean display. For example, in the latest version of the workbook, the pivot table is displaying one injury type at 10, another at 4, four others at 2, and five at a value of 1. My goal is to find a way to make a table that shows the injury types at 10, 4, and 2 broken out by name, and all the single injury types lumped together as "miscellaneous" with a cumulative total.

Thanks for your continued help.