Counting rows that meet multiple criteria
 

I have a strength report that generates counts based on multiple criteria.
The workbook has 2 sheets (Roster contains the personnel names and position
numbers; Report contains the number crunching). The only criteria I cannot
get to work is that only those rows that have data in the name field are
included in the count. Column A has the position number and column B has the
names. Not all positions are filled. I am only interested in counting rows of
data that meet both criteria.

How do I count rows that meet both criteria (correct number pattern and
non-blank)? In the example below, I am interested in all employees in the 500
series who are full-time employees (indicated by the third digit being a
"1"). This is my formula so far:

=COUNTIFS(Roster!A:A,"=01?-??", Roster!B:B, XXXXXXX)

A B C
1 521-01 Employee A NNNNNN
2 521-02
3 521-03 Employee B GGGGGG
4 521-04
5 522-01 Employee C VVVVVV
6 522-02 Employee D DDDDDD
7 522-03
8 522-04
9 531-01 Employee E KKKKKKK
10 531-02
11 531-03
12 532-01 Employee F LLLLLLLL
13 541-01 Employee G RRRRRR
14 541-02
15 541-03 Employee H YYYYYYY
16 542-01 Employee I EEEEEEE
17 542-02 Employee J WWWW
18 551-01 Employee K SSSSSS
19 551-02
20 551-03 Employee L FFFFFFF

I'm ready to pull my hair out : /

Counting rows that meet multiple criteria
 

An error in my original post. The formula I am using is:

=COUNTIFS(Roster!A:A,"=5?1-??", Roster!B:B, XXXXXXX)

What is the syntax for the criteria that will count only rows with a name
provided?

"sabow71" wrote:

I have a strength report that generates counts based on multiple criteria.
The workbook has 2 sheets (Roster contains the personnel names and position
numbers; Report contains the number crunching). The only criteria I cannot
get to work is that only those rows that have data in the name field are
included in the count. Column A has the position number and column B has the
names. Not all positions are filled. I am only interested in counting rows of
data that meet both criteria.

How do I count rows that meet both criteria (correct number pattern and
non-blank)? In the example below, I am interested in all employees in the 500
series who are full-time employees (indicated by the third digit being a
"1"). This is my formula so far:

=COUNTIFS(Roster!A:A,"=01?-??", Roster!B:B, XXXXXXX)

A B C
1 521-01 Employee A NNNNNN
2 521-02
3 521-03 Employee B GGGGGG
4 521-04
5 522-01 Employee C VVVVVV
6 522-02 Employee D DDDDDD
7 522-03
8 522-04
9 531-01 Employee E KKKKKKK
10 531-02
11 531-03
12 532-01 Employee F LLLLLLLL
13 541-01 Employee G RRRRRR
14 541-02
15 541-03 Employee H YYYYYYY
16 542-01 Employee I EEEEEEE
17 542-02 Employee J WWWW
18 551-01 Employee K SSSSSS
19 551-02
20 551-03 Employee L FFFFFFF

I'm ready to pull my hair out : /

Counting rows that meet multiple criteria
 

How do I count rows that meet both criteria
(correct number pattern and non-blank)?

Try it like this:

=COUNTIFS(Roster!A:A,"??1*", Roster!B:B, "<")

Assuming that blank cells in column B are *empty* cells.


--
Biff
Microsoft Excel MVP


"sabow71" wrote in message
...
I have a strength report that generates counts based on multiple criteria.
The workbook has 2 sheets (Roster contains the personnel names and
position
numbers; Report contains the number crunching). The only criteria I cannot
get to work is that only those rows that have data in the name field are
included in the count. Column A has the position number and column B has
the
names. Not all positions are filled. I am only interested in counting rows
of
data that meet both criteria.

How do I count rows that meet both criteria (correct number pattern and
non-blank)? In the example below, I am interested in all employees in the
500
series who are full-time employees (indicated by the third digit being a
"1"). This is my formula so far:

=COUNTIFS(Roster!A:A,"=01?-??", Roster!B:B, XXXXXXX)

A B C
1 521-01 Employee A NNNNNN
2 521-02
3 521-03 Employee B GGGGGG
4 521-04
5 522-01 Employee C VVVVVV
6 522-02 Employee D DDDDDD
7 522-03
8 522-04
9 531-01 Employee E KKKKKKK
10 531-02
11 531-03
12 532-01 Employee F LLLLLLLL
13 541-01 Employee G RRRRRR
14 541-02
15 541-03 Employee H YYYYYYY
16 542-01 Employee I EEEEEEE
17 542-02 Employee J WWWW
18 551-01 Employee K SSSSSS
19 551-02
20 551-03 Employee L FFFFFFF

I'm ready to pull my hair out : /

Counting rows that meet multiple criteria
 

Ooops!

employees in the 500 series

I missed that part!

Try this:

=COUNTIFS(Roster!A:A,"5?1*", Roster!B:B, "<")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
How do I count rows that meet both criteria
(correct number pattern and non-blank)?

Try it like this:

=COUNTIFS(Roster!A:A,"??1*", Roster!B:B, "<")

Assuming that blank cells in column B are *empty* cells.


--
Biff
Microsoft Excel MVP


"sabow71" wrote in message
...
I have a strength report that generates counts based on multiple criteria.
The workbook has 2 sheets (Roster contains the personnel names and
position
numbers; Report contains the number crunching). The only criteria I
cannot
get to work is that only those rows that have data in the name field are
included in the count. Column A has the position number and column B has
the
names. Not all positions are filled. I am only interested in counting
rows of
data that meet both criteria.

How do I count rows that meet both criteria (correct number pattern and
non-blank)? In the example below, I am interested in all employees in the
500
series who are full-time employees (indicated by the third digit being a
"1"). This is my formula so far:

=COUNTIFS(Roster!A:A,"=01?-??", Roster!B:B, XXXXXXX)

A B C
1 521-01 Employee A NNNNNN
2 521-02
3 521-03 Employee B GGGGGG
4 521-04
5 522-01 Employee C VVVVVV
6 522-02 Employee D DDDDDD
7 522-03
8 522-04
9 531-01 Employee E KKKKKKK
10 531-02
11 531-03
12 532-01 Employee F LLLLLLLL
13 541-01 Employee G RRRRRR
14 541-02
15 541-03 Employee H YYYYYYY
16 542-01 Employee I EEEEEEE
17 542-02 Employee J WWWW
18 551-01 Employee K SSSSSS
19 551-02
20 551-03 Employee L FFFFFFF

I'm ready to pull my hair out : /

Counting rows that meet multiple criteria
 

OMG!!!!! "<" That's it?!!!!

Thank you very much.

"T. Valko" wrote:

Ooops!

employees in the 500 series

I missed that part!

Try this:

=COUNTIFS(Roster!A:A,"5?1*", Roster!B:B, "<")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
How do I count rows that meet both criteria
(correct number pattern and non-blank)?

Try it like this:

=COUNTIFS(Roster!A:A,"??1*", Roster!B:B, "<")

Assuming that blank cells in column B are *empty* cells.


--
Biff
Microsoft Excel MVP


"sabow71" wrote in message
...
I have a strength report that generates counts based on multiple criteria.
The workbook has 2 sheets (Roster contains the personnel names and
position
numbers; Report contains the number crunching). The only criteria I
cannot
get to work is that only those rows that have data in the name field are
included in the count. Column A has the position number and column B has
the
names. Not all positions are filled. I am only interested in counting
rows of
data that meet both criteria.

How do I count rows that meet both criteria (correct number pattern and
non-blank)? In the example below, I am interested in all employees in the
500
series who are full-time employees (indicated by the third digit being a
"1"). This is my formula so far:

=COUNTIFS(Roster!A:A,"=01?-??", Roster!B:B, XXXXXXX)

A B C
1 521-01 Employee A NNNNNN
2 521-02
3 521-03 Employee B GGGGGG
4 521-04
5 522-01 Employee C VVVVVV
6 522-02 Employee D DDDDDD
7 522-03
8 522-04
9 531-01 Employee E KKKKKKK
10 531-02
11 531-03
12 532-01 Employee F LLLLLLLL
13 541-01 Employee G RRRRRR
14 541-02
15 541-03 Employee H YYYYYYY
16 542-01 Employee I EEEEEEE
17 542-02 Employee J WWWW
18 551-01 Employee K SSSSSS
19 551-02
20 551-03 Employee L FFFFFFF

I'm ready to pull my hair out : /

Counting rows that meet multiple criteria
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"sabow71" wrote in message
...
OMG!!!!! "<" That's it?!!!!

Thank you very much.

"T. Valko" wrote:

Ooops!

employees in the 500 series

I missed that part!

Try this:

=COUNTIFS(Roster!A:A,"5?1*", Roster!B:B, "<")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
How do I count rows that meet both criteria
(correct number pattern and non-blank)?

Try it like this:

=COUNTIFS(Roster!A:A,"??1*", Roster!B:B, "<")

Assuming that blank cells in column B are *empty* cells.


--
Biff
Microsoft Excel MVP


"sabow71" wrote in message
...
I have a strength report that generates counts based on multiple
criteria.
The workbook has 2 sheets (Roster contains the personnel names and
position
numbers; Report contains the number crunching). The only criteria I
cannot
get to work is that only those rows that have data in the name field
are
included in the count. Column A has the position number and column B
has
the
names. Not all positions are filled. I am only interested in counting
rows of
data that meet both criteria.

How do I count rows that meet both criteria (correct number pattern
and
non-blank)? In the example below, I am interested in all employees in
the
500
series who are full-time employees (indicated by the third digit being
a
"1"). This is my formula so far:

=COUNTIFS(Roster!A:A,"=01?-??", Roster!B:B, XXXXXXX)

A B C
1 521-01 Employee A NNNNNN
2 521-02
3 521-03 Employee B GGGGGG
4 521-04
5 522-01 Employee C VVVVVV
6 522-02 Employee D DDDDDD
7 522-03
8 522-04
9 531-01 Employee E KKKKKKK
10 531-02
11 531-03
12 532-01 Employee F LLLLLLLL
13 541-01 Employee G RRRRRR
14 541-02
15 541-03 Employee H YYYYYYY
16 542-01 Employee I EEEEEEE
17 542-02 Employee J WWWW
18 551-01 Employee K SSSSSS
19 551-02
20 551-03 Employee L FFFFFFF

I'm ready to pull my hair out : /