I want to get a count of the number of rows on a worksheet titled People where column G has the word Yes and Column S is blank.
I suspect I want a formula that is a bit like
=COUNTIF(People!G:G,"=Yes")*(People!S:S="")
but if I enter this as a normal formula then I just get the count of cells in G that have "Yes" and if I use Control-Shift-Enter to enter this as an array formula then nothing happens.
What is the correct syntax?
Answering my own question
The formula
=COUNTIF(People!G:G,"=Yes")- COUNTIF(People!S:S,">""""")
appears to get the value I want, but it would still be nice to know how to count the number of rows that match two specific criteria like this.
I never did understand array formulae
-
- Administrator
- Posts: 12631
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: I never did understand array formulae
Excel 2007 and later have a COUNTIFS function that let you specify multiple criteria:
=COUNTIFS(People!G:G,"Yes",People!S:S,"")
If you need compatibility with Excel 2003 and before, you can use SUMPRODUCT. This won't work with entire columns, so you must specify a limited range:
=SUMPRODUCT((People!G1:G60000="Yes")*(People!S1:S60000=""))
=COUNTIFS(People!G:G,"Yes",People!S:S,"")
If you need compatibility with Excel 2003 and before, you can use SUMPRODUCT. This won't work with entire columns, so you must specify a limited range:
=SUMPRODUCT((People!G1:G60000="Yes")*(People!S1:S60000=""))
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12631
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: I never did understand array formulae
Very helpful, thank you. And led me to realise that my attempt had the answer wrong by 1 as it had counted the header row in column S.
StuartR