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.

pivot tablesare your most likely solution. – Mike Fitzpatrick Jun 17 '10 at 10:13