COUNTIF Multiple Conditions

[Paul Sheppard]

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

[Rowan]

=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
>
>

[Don Guillett]

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
[email protected]
"Rowan" <[email protected]> wrote in message
news:[email protected]...
>
=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
> >
> >

[Domenic]

Maybe...

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!$B$2:$B$100,A6:A8,0))),--(Sheet1!$C$
2:$C$100<>""))

Hope this helps!

In article
<[email protected]>,
Paul Sheppard
<[email protected]> 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

[cchen212]

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%")

[Peo Sjoblom]

=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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>