Formula to count TWO conditions are met
 

I am relatively new to formulas with Excel. I have used 'COUNTIF' to count
how often a coloumn contains a certain line of text. However I want a formula
which will do the following (with a made up example);
Coloumn A is a pick-list of names - e.g. A Nother, J Bloggs etc
Coloumn B is a pick-list of queries - e.g. Late for Work, Sickness Day etc
I need to create a formula that would count how often the combination of 'A
Nother' & 'Sickness Day' was used, and another for how often the combination
of 'Joe Bloggs' and 'Late for work' was also used. This is for use in a table
& chart.

Many thanks.

Formula to count TWO conditions are met
 

Try

=SUMPRODUCT(--(A1:A10="A Nother"),--(B1:B10="Sickness Day"))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=574199

Formula to count TWO conditions are met
 

=SUMPRODUCT(--(A1:A100="A Nother");--(B1:B100="Sickness Day")

=SUMPRODUCT(--(A1:A100="Joe Bloggs");--(B1:B100="Late for work")

should work for you. You'll find SUMPRODUCT used a lot for this type of
conditional counting and summing.

"IoHeFy" wrote:

I am relatively new to formulas with Excel. I have used 'COUNTIF' to count
how often a coloumn contains a certain line of text. However I want a formula
which will do the following (with a made up example);
Coloumn A is a pick-list of names - e.g. A Nother, J Bloggs etc
Coloumn B is a pick-list of queries - e.g. Late for Work, Sickness Day etc
I need to create a formula that would count how often the combination of 'A
Nother' & 'Sickness Day' was used, and another for how often the combination
of 'Joe Bloggs' and 'Late for work' was also used. This is for use in a table
& chart.

Many thanks.

Formula to count TWO conditions are met
 

=SUMPRODUCT(--(A1:A100="A Nother"),--(B1:B100="Sickness day"))

=SUMPRODUCT(--(A1:A100="Joe Bloggs"),--(B1:B100="Late for work"))

You can replace literals with cells containing your values. Note SUMPRODUCT
cannot use whole columns i.e A:A is not allowed.

HTH

"IoHeFy" wrote:

I am relatively new to formulas with Excel. I have used 'COUNTIF' to count
how often a coloumn contains a certain line of text. However I want a formula
which will do the following (with a made up example);
Coloumn A is a pick-list of names - e.g. A Nother, J Bloggs etc
Coloumn B is a pick-list of queries - e.g. Late for Work, Sickness Day etc
I need to create a formula that would count how often the combination of 'A
Nother' & 'Sickness Day' was used, and another for how often the combination
of 'Joe Bloggs' and 'Late for work' was also used. This is for use in a table
& chart.

Many thanks.