Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statement
Hello all!
I need help on writing a CountIF statement for the following: I want a total count of serial numbers in column B that have discontinue in column E. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statement
Try this array formula: =SUM(IF((E1:E100="discontinue")*(B1:B1000),1)) entered using SHIFT+CTRL+ENTER HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=496219 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statement
The SUMPRODUCT worked. Thanks Elkar!
"Elkar" wrote: Depending on your needs, there are a couple different solutions he Use this first one if you just want a count of how many times "discontinue" appears in column E. =COUNTIF(E1:E100,"discontinue") Use this second one if you want a count of lines in which both a serial number appears in column B and "discontinue" appears in column E. =SUMPRODUCT(--(B1:B100<""),--(E1:E100="discontinue")) And of course, adjust the cell references to your needs. I just assumed rows 1 through 100 for the examples. HTH, Elkar "Tracy" wrote: Hello all! I need help on writing a CountIF statement for the following: I want a total count of serial numbers in column B that have discontinue in column E. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statement
Depending on your needs, there are a couple different solutions he
Use this first one if you just want a count of how many times "discontinue" appears in column E. =COUNTIF(E1:E100,"discontinue") Use this second one if you want a count of lines in which both a serial number appears in column B and "discontinue" appears in column E. =SUMPRODUCT(--(B1:B100<""),--(E1:E100="discontinue")) And of course, adjust the cell references to your needs. I just assumed rows 1 through 100 for the examples. HTH, Elkar "Tracy" wrote: Hello all! I need help on writing a CountIF statement for the following: I want a total count of serial numbers in column B that have discontinue in column E. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If statement | Excel Discussion (Misc queries) | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
How to calculate a sum as one outcome of an IF statement | Excel Worksheet Functions | |||
7+ nested if statement? | Excel Worksheet Functions | |||
Statement | Excel Worksheet Functions |