| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting cells using multiple criteria
Hi, how do a create a custom function to count cells (text) based on more than criteria. Example column a - lists critiera 1 (text) column b - list criteria 2 (text) I would like to be able to count number of cells from colulmn b that match citieria 1 and 2. I have tried multiple countif and counta and sum product but not getting the correct result -- st120869 ------------------------------------------------------------------------ st120869's Profile: http://www.excelforum.com/member.php...o&userid=18330 View this thread: http://www.excelforum.com/showthread...hreadid=517741 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting cells using multiple criteria
=sumproduct(--(rng1="text1"),--(rng2="text2"))
Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "st120869" wrote in message ... Hi, how do a create a custom function to count cells (text) based on more than criteria. Example column a - lists critiera 1 (text) column b - list criteria 2 (text) I would like to be able to count number of cells from colulmn b that match citieria 1 and 2. I have tried multiple countif and counta and sum product but not getting the correct result -- st120869 ------------------------------------------------------------------------ st120869's Profile: http://www.excelforum.com/member.php...o&userid=18330 View this thread: http://www.excelforum.com/showthread...hreadid=517741 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting cells using multiple criteria
THanks, I get a #value error. I ahve include some text below Reward no Reward no Reward Yes Reward Yes Reward Yes Reward Yes Reward Yes Reward Yes xx1 Yes the result I would expect to see if criteria is column A ="reward" & column B = "yes" would be 7 -- st120869 ------------------------------------------------------------------------ st120869's Profile: http://www.excelforum.com/member.php...o&userid=18330 View this thread: http://www.excelforum.com/showthread...hreadid=517741 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting cells using multiple criteria
=SUMPRODUCT(--(A1:A100="Reward"),--(B1:B100="Yes"))
works for me, but with a result of 6 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "st120869" wrote in message ... THanks, I get a #value error. I ahve include some text below Reward no Reward no Reward Yes Reward Yes Reward Yes Reward Yes Reward Yes Reward Yes xx1 Yes the result I would expect to see if criteria is column A ="reward" & column B = "yes" would be 7 -- st120869 ------------------------------------------------------------------------ st120869's Profile: http://www.excelforum.com/member.php...o&userid=18330 View this thread: http://www.excelforum.com/showthread...hreadid=517741 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting cells using multiple criteria
Bob, your maths is better than mine !!, I have recopied your formula but still getting a #value error - any clues?? -- st120869 ------------------------------------------------------------------------ st120869's Profile: http://www.excelforum.com/member.php...o&userid=18330 View this thread: http://www.excelforum.com/showthread...hreadid=517741 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
counting cells using multiple criteria
Not really, can you post the workbook somewhere?
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "st120869" wrote in message ... Bob, your maths is better than mine !!, I have recopied your formula but still getting a #value error - any clues?? -- st120869 ------------------------------------------------------------------------ st120869's Profile: http://www.excelforum.com/member.php...o&userid=18330 View this thread: http://www.excelforum.com/showthread...hreadid=517741 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting cells with multiple criteria | Excel Worksheet Functions | |||
| Counting cells using multiple criteria | Excel Worksheet Functions | |||
| Counting multiple cells using a criteria | Excel Discussion (Misc queries) | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
![st120869[_9_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
