Thank you all very much - My googling was turning up some fairly complicated formula/macro based fixes, and the PivotTable seems much more my speed. My only (minor) issue would be that ideally I would like to display AB 4, rather than having it broken down by A/B individually (ie have the data broken down by column A, but have column B displayed because it’s a description of what A is). That’s a very minor quibble though, so if there’s not an easy fix anyone suggests I’ll leave it alone (and I’ll look up some PivotTable tutorials - I think they could be very useful for some other things as well).
I’ll agree with those saying that Excel is not an ideal solution for this sort of thing, but it’s a document that has a number of different requirements and elements, some of which are suitable for excel and some of which aren’t. And besides, it’s a collaborative document and I doubt I’ll be able to convince others to find a more suitable software.
Basically, does A always go with B & C & D?
And if not:
- is there ever going to be a blank in any of the columns?
- if there are differences, do you want it only to add when all of the columns match, or when certain columns match
A will always go with B, but C and D may change. If A is blank, the whole row will be blank. If A is filled out, the rest will be filled out as well. I would prefer to have it so that only A has to match.
Also, you want the counts to update when the data updates - but how does the data get updated? is someone manually typing things in? is it getting updated automatically by a different program?
Someone will be manually making a change in excel. I know that they could update it manually, but there are a number of users, some of whom are not very good with excel (even compared to me, who had never heard of a PivotTable), so I would prefer to keep it as simple as possible. There seems to be a fairly easy copy/paste macro based fix to this issue in PivotTables, so I may attempt that.
Finally, what do you mean by “the section you’re interested in”? how do you identify that from the section you’re not interested in?
The document pulls together data from a number of different departments; I’m interested in the data from a specific department. That data is in a specific place, color-coded, and identified by a header.