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

=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

or slightly shorter might work

=SUMPRODUCT(($B$2:$B$100=A6)*($B$2:$B$100={A7,a8}) *($C$2:$C$100<""))

--
Don Guillett
SalesAid Software

"Rowan" wrote in message
...

=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$10
0<""))

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

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

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

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