Countif function over more than one column

  • Thread starter Thread starter Shirley
  • Start date Start date
S

Shirley

This is probably very easy for everyday users of Excel,
but I'm a novice!
I have a worksheet in which there are 2 columns from which
I need to extract particular data
eg
Column A Column B
Adrian Jelfs External Reference
Adrian Jelfs External Reference
Adrian Jelfs External Reference
Adrian Jelfs Complete
Adrian Jelfs Complete
Meg Booth External Reference
Meg Booth Complete
Meg Booth External Reference

This is just a small example - there are numerous rows
that will go in this spreadsheet.

I need to set up a Countif function that will give me the
result of the number of Adrian Jelfs (in ColumnA) that
have External Reference (in ColumnB) next to his name and
a separate count for Adrian Jelfs that has Complete next
to his name. And so on for each person's name that will
be entered in this spreadsheet.
What is the function that I need to set up?

Thank you
 
Shirley said:
This is probably very easy for everyday users of Excel,
but I'm a novice!
I have a worksheet in which there are 2 columns from which
I need to extract particular data
eg
Column A Column B
Adrian Jelfs External Reference
Adrian Jelfs External Reference
Adrian Jelfs External Reference
Adrian Jelfs Complete
Adrian Jelfs Complete
Meg Booth External Reference
Meg Booth Complete
Meg Booth External Reference

This is just a small example - there are numerous rows
that will go in this spreadsheet.

I need to set up a Countif function that will give me the
result of the number of Adrian Jelfs (in ColumnA) that
have External Reference (in ColumnB) next to his name and
a separate count for Adrian Jelfs that has Complete next
to his name. And so on for each person's name that will
be entered in this spreadsheet.
What is the function that I need to set up?

Thank you
Click to expand...

=SUMPRODUCT(($A$1:$A$100="Adrian Jelfs")*($B$1:$B$100="External Reference"))
or
=SUMPRODUCT(($A$1:$A$100=A120)*($B$1:$B$100=B120))
where A120 (for example) contains the text "Adrian Jelfs" (without the
quotes) and B120 contains "External Reference".
This latter form is useful as you can set up the criteria in a list (A120,
B120, A121, B121 and so on) with a formula in C120 that can then just be
copied down to C121, C122 etc.
 
Which is fine if the number of names is small, but what if
there are hundreds? Easier to let Excel do the work in a
PIVOT table.
 
Back
Top