Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have 30 colleagues who are answering questions over 3 time periods. Each has their own Excel workbook with the questions, and over the year they update it. I collate their worksheets into one master worksheet, but now need to combine their answers into a simple table. The questions, the time periods and then a COUNT of how many answered it.

For example: I need a table that shows me how many people (not the persons name at this point) answered question 10 in time period 2.

I can't use a database before someone mentions it!

alt text

share|improve this question
    
Your image shows names, not counts, of colleagues so I'm a bit unclear but I reckon pivot tables are your most likely solution. –  Mike Fitzpatrick Jun 17 '10 at 10:13
add comment

2 Answers 2

up vote 1 down vote accepted

You can do this by using multiple worksheets in a workbook.

Instead of repeating each person along the top, give them each their own worksheet.

You can then create a Summary worksheet and use the COUNTA function referencing each of the other worksheets

Using your example, if Questions is in Cell A1 and you instead have a worksheet for Michael and one for Bob then in your Summary worksheet you could do this in Summary cell B2:

=COUNTA(Michael!B2)+COUNTA(Bob!B2)

Which will give you Question 1 count for Jan-Mar

I've added some screenshots to illustrate:

enter image description here

enter image description here

enter image description here

enter image description here

share|improve this answer
    
I'm not sure if this allows me to combine Time Periods. Everything in Jan-Mar needs to be added together for each qustion, but not all time periods for a question. –  RocketGoal Jun 17 '10 at 11:48
    
ah, OK, I looked more closely at your image. You basically have 3 axis of data and are representing the third axis by repeating the 2nd for each person against the first... Sorry, my answer is not what you need then. –  Shevek Jun 17 '10 at 12:59
    
@Mike - updated answer for you! –  Shevek Jun 17 '10 at 13:09
    
Thanks, Í like the structure behind this one. Although the formula may become a little cumbersome with my 30+ colleagues I still think it's manageable. –  RocketGoal Jun 17 '10 at 15:04
    
you could probably create a macro which would loop through all the worksheets and generate the formula for you. You could do it just for cell B2 then use Excel's Fill functionality to fill the rest. –  Shevek Jun 17 '10 at 15:24
add comment

The simplest and most obvious way (to me) would be to use COUNTA() - this counts how many cells have an alpha-numeric value (ie, numbers or text) - and specify exactly which set of cells you wish to check against.

So the count for [Question10,Apr-Sept] could be (assuming A1 is the top-left cell in your image) =COUNTA(C11,F11) - just add more commas and cells as needed.

It might require a little manual mess around with the inital formula (especially if you have lots of repeated time-period columns), but set it up for [Q1,Period1] and you should then be able to drag fill this formula to get results over your entire set of questions/times. Just bare in mind it requires all the formulae to be manually adjusted if you add more columns.


Update:

And here's a more advanced version that uses an array formula (it might not be the simplest, so has anyone got any tidier offerings?). It means you don't need to worry so much about manually adjusting formula further down the line. It counts how many text entries are found in every nth cell of a particular row.

=COLUMN($G11)-COLUMN($B11)+1-COUNT(IF(MOD(COLUMN($B11:$G11)-COLUMN($B11)+1,3)=1,$B11:$G11,0))

Note: To use an array formula you must use Crtl+Shift+Enter when you've done typing in the formula (it will be shown surrounded by curly braces).

To use this for your purposes:

  • Replace every occurance of B11 with the first data cell in the row.
  • Replace every occurance of G11 with the last data cell in the row OR to allow yourself to add rows at random set this it use a column way off to the right (eg IV11).
  • The 3 near the end (in the MOD statement) is the number of time periods. You won't need to touch this if you don't add more time periods.
  • The =1 (after the MOD statement) is the time period to extract (with 0 being the first time period, 1 being the second time period, etc).

If I've got it right, the rest could sort itself out.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.