COUNT using multiple conditions
I need excel to count the number of rows which contain both of two conditions in seperate cells. the condtions are a specified number AND a specified letter, if the row only contains one of either conditions i need excel to ignore it, but if it contains both then count it as one. I have used the SUMPRODUCT function but it doesnt seem to work, im guessing because one of the conditions is text not numerical any ideas? many thanks -- SamGB ------------------------------------------------------------------------ SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263 View this thread: http://www.excelforum.com/showthread...hreadid=510798 |
COUNT using multiple conditions
Are these values in specific columns, or are you saying you want to test if
the value is in any cell in the row? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "SamGB" wrote in message ... I need excel to count the number of rows which contain both of two conditions in seperate cells. the condtions are a specified number AND a specified letter, if the row only contains one of either conditions i need excel to ignore it, but if it contains both then count it as one. I have used the SUMPRODUCT function but it doesnt seem to work, im guessing because one of the conditions is text not numerical any ideas? many thanks -- SamGB ------------------------------------------------------------------------ SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263 View this thread: http://www.excelforum.com/showthread...hreadid=510798 |
COUNT using multiple conditions
Sam
a couple of ways: =SUMPRODUCT((A2:A29=1)*(B2:B29="a")) =SUMPRODUCT(--(A2:A29=1),--(B2:B29="a")) Same effect, slightly different ways of doing it Note that =SUMPRODUCT((A2:A29=1),(B2:B29="a")) does *not* work Regards Trevor "SamGB" wrote in message ... I need excel to count the number of rows which contain both of two conditions in seperate cells. the condtions are a specified number AND a specified letter, if the row only contains one of either conditions i need excel to ignore it, but if it contains both then count it as one. I have used the SUMPRODUCT function but it doesnt seem to work, im guessing because one of the conditions is text not numerical any ideas? many thanks -- SamGB ------------------------------------------------------------------------ SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263 View this thread: http://www.excelforum.com/showthread...hreadid=510798 |
All times are GMT +1. The time now is 07:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com