Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Hi
I am trying to work out how to create a formula that reads only entries of "Open" in column A and counts instances of those with "Open" and a value of over 10 in column B i.e. a value of 2 should be returned in the example below. Can anyone please help Cheers Ross A B C D 1open 11 2closed 11 3open 16 4open 7 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
=SUM((A1:A6="open")*(B1:B610))
this is an array formula, enter it with ctrl+shift+enter. change the range to your needs. hth carlo On Nov 22, 9:23 am, HighlandRoss wrote: Hi I am trying to work out how to create a formula that reads only entries of "Open" in column A and counts instances of those with "Open" and a value of over 10 in column B i.e. a value of 2 should be returned in the example below. Can anyone please help Cheers Ross A B C D 1open 11 2closed 11 3open 16 4open 7 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
another suggestion:
=Sumproduct(--(A1:A4="open"), --(B1:B410)) entered normally "HighlandRoss" wrote: Hi I am trying to work out how to create a formula that reads only entries of "Open" in column A and counts instances of those with "Open" and a value of over 10 in column B i.e. a value of 2 should be returned in the example below. Can anyone please help Cheers Ross A B C D 1open 11 2closed 11 3open 16 4open 7 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Thanks JMB.
If I were to have the formula on a different sheet to the data how would that be done? "JMB" wrote: another suggestion: =Sumproduct(--(A1:A4="open"), --(B1:B410)) entered normally "HighlandRoss" wrote: Hi I am trying to work out how to create a formula that reads only entries of "Open" in column A and counts instances of those with "Open" and a value of over 10 in column B i.e. a value of 2 should be returned in the example below. Can anyone please help Cheers Ross A B C D 1open 11 2closed 11 3open 16 4open 7 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|