Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
count non empty cells if other cell has certain value
Hello,
I'm trying to do the following: col-a col-b 1 d 2 x 2 s 2 3 d In a cell I want to count the number of non-empty cells in col-b where the value of col-a is 2. The answer for this example should be 2 because there are 3 rows with value 2 in col-a but only 2 with a value in col-b. I tried all sorts of ways (like counta, sumif etc.) but somehow I'm not succeeding. Can anyone give me a hint ? Thanks in advance, Nico |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
count non empty cells if other cell has certain value
Hi
Try this: =SUMPRODUCT((A2:A6=2)*(B2:B6<"")) This function cannot use full columns as references and the ranges must be the same size. Hope this helps. Andy. "Nico" wrote in message ... Hello, I'm trying to do the following: col-a col-b 1 d 2 x 2 s 2 3 d In a cell I want to count the number of non-empty cells in col-b where the value of col-a is 2. The answer for this example should be 2 because there are 3 rows with value 2 in col-a but only 2 with a value in col-b. I tried all sorts of ways (like counta, sumif etc.) but somehow I'm not succeeding. Can anyone give me a hint ? Thanks in advance, Nico |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
count non empty cells if other cell has certain value
Andy,
My example was not complete, because in the first column there can also be letters. If this is the case then the formule doesn't work. If I try it with numbers, it works fine. Any way to work around this ? Thanks for your response. Nico "Andy" wrote: Hi Try this: =SUMPRODUCT((A2:A6=2)*(B2:B6<"")) This function cannot use full columns as references and the ranges must be the same size. Hope this helps. Andy. "Nico" wrote in message ... Hello, I'm trying to do the following: col-a col-b 1 d 2 x 2 s 2 3 d In a cell I want to count the number of non-empty cells in col-b where the value of col-a is 2. The answer for this example should be 2 because there are 3 rows with value 2 in col-a but only 2 with a value in col-b. I tried all sorts of ways (like counta, sumif etc.) but somehow I'm not succeeding. Can anyone give me a hint ? Thanks in advance, Nico |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
count non empty cells if other cell has certain value
Hi
With the letters, instead of 2 use "a" - or whatever: =SUMPRODUCT((A2:A6="a")*(B2:B6<"")) Andy. "Nico" wrote in message ... Andy, My example was not complete, because in the first column there can also be letters. If this is the case then the formule doesn't work. If I try it with numbers, it works fine. Any way to work around this ? Thanks for your response. Nico "Andy" wrote: Hi Try this: =SUMPRODUCT((A2:A6=2)*(B2:B6<"")) This function cannot use full columns as references and the ranges must be the same size. Hope this helps. Andy. "Nico" wrote in message ... Hello, I'm trying to do the following: col-a col-b 1 d 2 x 2 s 2 3 d In a cell I want to count the number of non-empty cells in col-b where the value of col-a is 2. The answer for this example should be 2 because there are 3 rows with value 2 in col-a but only 2 with a value in col-b. I tried all sorts of ways (like counta, sumif etc.) but somehow I'm not succeeding. Can anyone give me a hint ? Thanks in advance, Nico |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
count non empty cells if other cell has certain value
Andy,
Thanks very much, it works just like I wanted. And if you see it, it always looks so easy... ;-) Grtz, Nico "Andy" wrote: Hi With the letters, instead of 2 use "a" - or whatever: =SUMPRODUCT((A2:A6="a")*(B2:B6<"")) Andy. "Nico" wrote in message ... Andy, My example was not complete, because in the first column there can also be letters. If this is the case then the formule doesn't work. If I try it with numbers, it works fine. Any way to work around this ? Thanks for your response. Nico "Andy" wrote: Hi Try this: =SUMPRODUCT((A2:A6=2)*(B2:B6<"")) This function cannot use full columns as references and the ranges must be the same size. Hope this helps. Andy. "Nico" wrote in message ... Hello, I'm trying to do the following: col-a col-b 1 d 2 x 2 s 2 3 d In a cell I want to count the number of non-empty cells in col-b where the value of col-a is 2. The answer for this example should be 2 because there are 3 rows with value 2 in col-a but only 2 with a value in col-b. I tried all sorts of ways (like counta, sumif etc.) but somehow I'm not succeeding. Can anyone give me a hint ? Thanks in advance, Nico |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
count non empty cells if other cell has certain value
Glad to help - and thanks for the feedback!
Andy. "Nico" wrote in message ... Andy, Thanks very much, it works just like I wanted. And if you see it, it always looks so easy... ;-) Grtz, Nico "Andy" wrote: Hi With the letters, instead of 2 use "a" - or whatever: =SUMPRODUCT((A2:A6="a")*(B2:B6<"")) Andy. "Nico" wrote in message ... Andy, My example was not complete, because in the first column there can also be letters. If this is the case then the formule doesn't work. If I try it with numbers, it works fine. Any way to work around this ? Thanks for your response. Nico "Andy" wrote: Hi Try this: =SUMPRODUCT((A2:A6=2)*(B2:B6<"")) This function cannot use full columns as references and the ranges must be the same size. Hope this helps. Andy. "Nico" wrote in message ... Hello, I'm trying to do the following: col-a col-b 1 d 2 x 2 s 2 3 d In a cell I want to count the number of non-empty cells in col-b where the value of col-a is 2. The answer for this example should be 2 because there are 3 rows with value 2 in col-a but only 2 with a value in col-b. I tried all sorts of ways (like counta, sumif etc.) but somehow I'm not succeeding. Can anyone give me a hint ? Thanks in advance, Nico |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count for empty cells | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
count not empty cell | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |