Excel: What formula combines this data into one COUNT amount?

Question

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!

Answer 1

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:

Answer 2

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 n^th 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.