How to use counting formulas in Excel?

August 12, 2007 8:19 AM

I need help with counting formulas in Excel!

I run a customer service department for a furniture company, our furniture sells in a number of shops across the UK. I have to provide reports for what sort of trends there are for the problems that our customers experience.

My knowledge of Excel is unfortunately limited. I keep records for the individual service requests in Microsoft Access and export the database table into Excel to do my reports. So far, I have set up various charts and stuff, using formulas that link back to the source data imported from Access. The idea is to set all the charts and tables up so that automatically calculate each time I import an updated version of the database. However, there is one more table I want to set up but it is leaving me stuck.

Right, I don't have an image to show so you will have to use the powers of imagination! Starting at A1 (with no column headers) there are 2 columns of data with each row within representing a single service request record. The first column is the name of the store where the furniture was bought and the second column is for the fault classification e.g. Missing Part, Ordered Incorrectly, Broken Frame etc. Next to the 2 data columns I have created a table with all the types of fault classification along the top (26 in all!) and down the side are all the names of the stores. I need to input formulas to count how many times each store receives a complaint on each fault classification.

I apologise if I am not making sense, I can give more detail if needed.

Please help! I've gotta get back in the office first thing tomorrow and finish it.

posted by Lilou to computers & internet (13 comments total)

This is really the sort of thing that would be easier as a report in Access.
posted by grouse at 8:28 AM on August 12, 2007

To be honest, this is something that you would do in Access, I would have thought.

I'll try to explain this off the top of my head, not having Access handy at the moment. Make a query - make it a Group By query (The little ∑ symbol in the tool bar). Add store and fault field, set them to "group by", then add the store and fault fields again, and set them to "count". Something like that. It should produce output with a count of each store / fault combination.
posted by Jimbob at 8:30 AM on August 12, 2007

It makes sense. Let me work out the details. It is still AM here and i need coffee.
posted by lampshade at 8:34 AM on August 12, 2007

Sending to your email. Will post a solution here as soon as I can write it out and make sense.

I used SUMPRODUCT and COUNTIF
posted by lampshade at 9:08 AM on August 12, 2007

Based on what you're writing, I think you can use Pivot Table to accomplish what you want.

They would be difficult to explain in this forum, however.

You might try the built-in help feature, or check out

http://office.microsoft.com/en-us/excel/HA010346321033.aspx
posted by Gorgik at 9:09 AM on August 12, 2007

For future reference, you might find Mr Excel - the people on there are very helpful.
posted by paduasoy at 9:31 AM on August 12, 2007

^ useful
posted by paduasoy at 9:31 AM on August 12, 2007

This is the detail. As also suggested, a pivot would work, but this allows better manipulation for charts. Or at least I would like to think that it does.

This won't make sense without the pic and the image is not embedding so there is a link. The formulas look like this.
COUNTIF(A:A,E9)
SUMPRODUCT(--($A$3:$A$30=$E18),--($B$3:$B$30>=F$17))


Click for detail. (new window)
posted by lampshade at 9:31 AM on August 12, 2007 [1 favorite]

Ooops...there was cut off.

The formula at the bottom right is

Formula in J22
Store D, Damaged SUMPRODUCT(--($A$3:$A$30=$E22),--($B$3:$B$30>=J$17))


by the way, the double dashes are necessary and this is not an array formula. Just enter it and it goes.
posted by lampshade at 9:36 AM on August 12, 2007

I second Gorgik's Pivot Table suggestion. They're quick to set up and easy to update.
posted by jocelmeow at 9:36 AM on August 12, 2007

Not the original asker, but that is great lampshade. Any chance you could give a little insight into the "why" of your formulas? Particularly the double dashes and the greater than signs?
posted by Rock Steady at 9:42 AM on August 12, 2007

Here is a good page on the double dash reason. Better explanation that I could write. It is kinda geeky, but if you work with the formula, it will make sense eventually.

Also, I noted that the formula was not an array. This does not mean SUMPRODUCT does not work with arrays. Just in this case, there is no need for the CTRL-ENTER when finishing the formula.

Double Dash via tech-archive.net

If you, or anybody else for that matter, wants the sample in the image, leave a message here. I will email or upload it somewhere.

And the Mr Excel pages are great.

Also OzGrid, Contextures, Chip Pearson and many more.
posted by lampshade at 9:57 AM on August 12, 2007 [1 favorite]

did it ever work?

Just wondering. It's always interesting to know if a posted solution works out.
posted by lampshade at 3:46 PM on August 16, 2007