Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i use a formula count of figures between to conditions | Excel Worksheet Functions | |||
Count using 2 conditions, one of which being a "less than or equal to" - URGENT | Excel Discussion (Misc queries) | |||
COUNT using multiple conditions | Excel Discussion (Misc queries) | |||
Count If Formula | Excel Discussion (Misc queries) | |||
Formula format for Count or Countif funtion with two criterias | Excel Worksheet Functions |