Count Question

[mattie]

I need some help doing a countif, on my worksheet i have a list of jobs i have carried out for other departments what i am looking to do is count how many times a department has requested a certain type of work. In column F i have all my areas and in H i have the work, i cant seemed to get a countif to work to give me the correct figures.

Can anyone help me with this one please, i have put a little example of what data am working with below

Column F Column H
PS US
PS BAU
BUS BAU
SER US

 

[Aladin Akyurek]

I need some help doing a countif, on my worksheet i have a list of jobs i have carried out for other departments what i am looking to do is count how many times a department has requested a certain type of work. In column F i have all my areas and in H i have the work, i cant seemed to get a countif to work to give me the correct figures.

Can anyone help me with this one please, i have put a little example of what data am working with below

Column F Column H
PS US
PS BAU
BUS BAU
SER US

What are the concrete results that you expect?

 

[mattie]

I am creating a table above the list as a quick overview of the work i've been doing. For example in a table using examples in my original post it would show that....

Area US BAU
PS 1 1
BUS 0 1
SER 1 0

hope that makes sense

 

[Expiry]

A countif can only count one column, so you can use it to count how many times PS appears in col F or how many times US appears in col H, but not both.

Two ways around this:

1. Concatenate those into another column. So in an empty col, say col M, put =F2&H2.
then use =Countif(M:M,"PSUS")

2. Use Sumproduct
=SUMPRODUCT((F1:F14="PS")*(H1:H14="US"))

 

[Aladin Akyurek]

I am creating a table above the list as a quick overview of the work i've been doing. For example in a table using examples in my original post it would show that....

Area US BAU
PS 1 1
BUS 0 1
SER 1 0

hope that makes sense

J2: PS
J3: BUS
J4: SER

K1: US
L1: BAU

In K2 enter, copy across, and down:

=SUMPRODUCT(--($F$2:$F$5=$J2),--($H$2:$H$5=K$1))