countif or sumproduct ?
 

I have job titles in H2:H54 ( 10 various)
Some are occupied (names in B2:B34)
Some are vacant (B35:B54) nothing in B35:B54
I'd like a formula to count how many are occupied by title, and how many are
vacant by title.

Thanks,

Steve

countif or sumproduct ?
 

=SUMPRODUCT((H2:H54="job titles")*(B2:B54="occupied"))


"Steve" wrote:

I have job titles in H2:H54 ( 10 various)
Some are occupied (names in B2:B34)
Some are vacant (B35:B54) nothing in B35:B54
I'd like a formula to count how many are occupied by title, and how many are
vacant by title.

Thanks,

Steve

countif or sumproduct ?
 

Hi,

You may create a pivot table. Drag title to the row area and column B data
to the data area

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve" wrote in message
...
I have job titles in H2:H54 ( 10 various)
Some are occupied (names in B2:B34)
Some are vacant (B35:B54) nothing in B35:B54
I'd like a formula to count how many are occupied by title, and how many
are
vacant by title.

Thanks,

Steve

countif or sumproduct ?
 

Thank you. In my haste, I wasn't totally clear on what I needed, but while
waiting, another thread helped. This is what produced what I needed, counting
how many occupied or vacant for each job title( refs B3 dragged down and B11
dragged down:

=SUMPRODUCT(N('WC'!$H$2:$H$54=B3),N('WC'!$B$2:$B$5 40)) - for the occupied
and
=SUMPRODUCT(N('WC'!$H$2:$H$54=B11),N('WC'!$B$2:$B$ 54<=0)) - for the vacant

What is the N mean ?

Thanks,

"Teethless mama" wrote:

=SUMPRODUCT((H2:H54="job titles")*(B2:B54="occupied"))


"Steve" wrote:

I have job titles in H2:H54 ( 10 various)
Some are occupied (names in B2:B34)
Some are vacant (B35:B54) nothing in B35:B54
I'd like a formula to count how many are occupied by title, and how many are
vacant by title.

Thanks,

Steve

countif or sumproduct ?
 

Thank you, but I don't know pivot tables yet.

"Ashish Mathur" wrote:

Hi,

You may create a pivot table. Drag title to the row area and column B data
to the data area

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve" wrote in message
...
I have job titles in H2:H54 ( 10 various)
Some are occupied (names in B2:B34)
Some are vacant (B35:B54) nothing in B35:B54
I'd like a formula to count how many are occupied by title, and how many
are
vacant by title.

Thanks,

Steve