Home |
Search |
Today's Posts |
#1
|
|||
|
|||
COUNTIF Multiple Conditions
How do I use countif with multiple conditions Column B has dates Column C has Various data or Blank I need to count the number of non blanks in column C if column A is one of 3 dates I have tried the array formula below, but am getting the wrong answer {=SUM((Sheet1!$B$2:$B$100=A6)*(Sheet1!$B$2:$B$100= A7)*(Sheet1!$B$2:$B$100=A8)*(Sheet1!$C$2:$C$100<" "))} A6, A7 and A8 are the cells witth the dates in -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=468131 |
#2
|
|||
|
|||
=SUM(((Sheet1!$B$2:$B$100=A6)+(Sheet1!$B$2:$B$100= A7)+(Sheet1!$B$2:$B$100=A8))*(Sheet1!$C$2:$C$100< ""))
array entered with Ctrl+Shift+Enter or entered normally =SUMPRODUCT((($B$2:$B$100=A6)+($B$2:$B$100=A7)+($B $2:$B$100=A8))*($C$2:$C$100<"")) Hope this helps Rowan Paul Sheppard wrote: How do I use countif with multiple conditions Column B has dates Column C has Various data or Blank I need to count the number of non blanks in column C if column A is one of 3 dates I have tried the array formula below, but am getting the wrong answer {=SUM((Sheet1!$B$2:$B$100=A6)*(Sheet1!$B$2:$B$100= A7)*(Sheet1!$B$2:$B$100=A8)*(Sheet1!$C$2:$C$100<" "))} A6, A7 and A8 are the cells witth the dates in |
#4
|
|||
|
|||
Maybe...
=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!$B$2:$B$100,A6:A8,0))),--(Sheet1!$C$ 2:$C$100<"")) Hope this helps! In article , Paul Sheppard wrote: How do I use countif with multiple conditions Column B has dates Column C has Various data or Blank I need to count the number of non blanks in column C if column A is one of 3 dates I have tried the array formula below, but am getting the wrong answer {=SUM((Sheet1!$B$2:$B$100=A6)*(Sheet1!$B$2:$B$100= A7)*(Sheet1!$B$2:$B$100=A8)* (Sheet1!$C$2:$C$100<""))} A6, A7 and A8 are the cells witth the dates in |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF Multiple Conditions
Hello I want to count everything in Column K that is either greater than or = to 75% AND less than or equal to 125%. Is there a way to do this? This is the formula i used and it works but it seems a bit long winded. thanks =COUNT(K3:K29)-COUNTIF(K3:K29,"<75%")-COUNTIF(K3:K29,"125%") -- cchen212 ------------------------------------------------------------------------ cchen212's Profile: http://www.excelforum.com/member.php...o&userid=29951 View this thread: http://www.excelforum.com/showthread...hreadid=468131 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF Multiple Conditions
=COUNTIF(K3:K29,"=0.75")-COUNTIF(K3:K29,"1.25")
or =SUMPRODUCT(--(K3:K29=0.75),--(K3:K29<=1.25)) will both retrun the same count -- Regards, Peo Sjoblom "cchen212" wrote in message ... Hello I want to count everything in Column K that is either greater than or = to 75% AND less than or equal to 125%. Is there a way to do this? This is the formula i used and it works but it seems a bit long winded. thanks =COUNT(K3:K29)-COUNTIF(K3:K29,"<75%")-COUNTIF(K3:K29,"125%") -- cchen212 ------------------------------------------------------------------------ cchen212's Profile: http://www.excelforum.com/member.php...o&userid=29951 View this thread: http://www.excelforum.com/showthread...hreadid=468131 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple conditions on a countif | Excel Worksheet Functions | |||
multiple conditions in COUNTIF (Excel 2000) | Excel Discussion (Misc queries) | |||
COUNTIF Statement with Multiple Conditions in Different Ranges | Excel Worksheet Functions | |||
SUM based on multiple conditions - SORRY, URGENT!!! | Excel Worksheet Functions | |||
COUNTIF using multiple conditions? | New Users to Excel |