1

I'm trying to create a statistics report that gets automatically updated as data is put into a corresponding worksheet. So this being said, there's no telling how much data will be put into the database, so I need to reference entire columns (A:H), instead of closed arrays (A1:H70).

I need the counts, for example, of women who have cancer and live in the city.

Even more, I have a lot of columns, and it happens that sex and whether or no they have cancer will be on totally different sides of the spreadsheet, meaning I have to have a huge array full of data I don't care about, and I'm not sure how to control for that (or if I even need to - maybe I'm just confusing myself somehow?).

To further clarify what I am trying to do, I know that if I were programming this in SAS, I would use Proc Print's Where clause in one easy breezy beautiful line.

Any ideas as to what Excel function I need to use to do this? I've run through a gambit of ideas, trying to use VLOOKUP, ARRAY, MATCH, and a nested IF tree, but all of them ran into dead ends along the way. Thank you for any ideas you might have!

KayleeSeranada
  • 13
  • 1
  • 1
  • 3
  • 1
    Sounds like `COUNTIFS()` might get you close. Or you can use SQL directly on the table, which might allow you to use syntax more similar to what you're already familiar with. http://exceluser.com/formulas/msquery-excel-relational-data.htm – Tim Williams Dec 19 '13 at 23:13

1 Answers1

4

Sounds like you need COUNTIFS function, e.g. this formula will count the number of rows that have "x" in column A and "y" in column B

=COUNTIFS(A:A,"x",B:B,"y")

You can add up to 127 criteria using that format, rows are only counted if all criteria are fulfilled

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • I've gotten so used to thinking of `COUNTIFS` as a SQL `COUNT` with a `WHERE` clause I mentally recite "COUNT WHERE column A = x AND column B = y etc." when I write the formulae. – andy holaday Dec 20 '13 at 01:42
  • Ah, perfect!! I didn't realise that there was a COUNTIF for multiple criteria, thank you so much! – KayleeSeranada Dec 20 '13 at 04:11