2

I have a spreadsheet of data which lists the inspection results of schools, in which I have a column that states the result or is empty if that particular school has not yet been inspected.

I want there to be a cell which returns the number of schools that have been inspected. I assume that the best way to do this is to check whether or not the 'inspection result' cell has anything in it, and if it does add 1 to this 'total inspected' cell. I've tried writing an IF statement but it isn't working:

=IF(
   OR(
     'Indiviual FS Data'!D2:D174 === 'Good (2)',
     'Indiviual FS Data'!D2:D174 === 'Outstanding (1)',
     'Indiviual FS Data'!D2:D174 === 'Requires Improvement (3)', 
     'Indiviual FS Data'!D2:D174 === 'Inadequate (4)'
   )=,
   C3+1, C3
 )

This may not make sense as you can't see the spreadsheet.

I have some very minor knowledge of programming concepts but I don't have any experience with this or similar in Excel.

4

I would just use

=COUNTIF(D2:D174, "<>")

It counts all cells in the range which aren't empty.

About your formula:

  • You cannot use the === operator (or ==). You only use =.
  • You don't use a single quote (') for strings, use double-quotes (").
  • You have one extra = after your ending OR parenthesis.
  • You cannot use a range in a comparison in the way you do, compare only one cell.
| improve this answer | |
  • 1
    What does <> do in this context? – Vidar S. Ramdal Feb 24 '14 at 12:34
  • 2
    It means "Not equal to", and since nothing follows, it means "Not equal to nothing". It counts cells that aren't empty. You could write "<>Hi" to look for cells not containing Hi. – Punchlinern Feb 24 '14 at 12:35
  • Ah, that's great. Could you supply a link to the documentation for the <> operator? I don't think I have seen it before. – Vidar S. Ramdal Feb 25 '14 at 9:00
  • 1
    Google has a rather limited documentation on it's formulas. I'm not sure if there's any documentation about that, read more here: webapps.stackexchange.com/questions/15727/… – Punchlinern Feb 25 '14 at 9:04
  • This is great, exactly what I was looking for, thanks! – ktweeden Feb 25 '14 at 13:27
1

One way of doing this is to count the number of rows (schools), then subtract the number of blank cells.

Given the following spreadsheet:

    A             | B
1 | School name   | Inspected date
-----------------------------------
2 | School 1      | 2/24/2014
3 | School 2      | 
4 | School 3      | 1/5/2014

... the following formula will give the number of schools that have been inspected:

=ROWS(B2:B4) - COUNTBLANK(B2:B4)

For your spreadsheet, you would use =ROWS(D2:D174) - COUNTBLANK(D2:D174)

The ROWS function gives the number of rows in the given range (3). The COUNTBLANK function gives the number of blank cells in the given range (1). So =3-1 will give the result 2.

I have set up an example spreadsheet to demonstrate.

See the Google Spreadsheet Function list for details on how the functions work.

| improve this answer | |
  • 1
    Is this preferable to my answer in any way? It seems to to me to be doing the exact same thing, only with an extra function. – Punchlinern Feb 25 '14 at 8:44
  • No, you're probably right. I wrote it because I didn't understand the <> in your answer, and wanted to supply something less cryptic. But your comment to your answer clarified it nicely. – Vidar S. Ramdal Feb 25 '14 at 8:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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