Thread: CountIf trouble

Hi I am having a time with Countif statement. Here is what I have

I have a table with some rows that I need to count

Row F
Job Type RowG
Order Status
Sys Ordered
Prg Quoted
Sys Quoted
Prg Ordered
Sys Ordered
Prg Quoted
Sys Quoted
Prg Quoted
Sys Quoted
Sys
Sys Quoted
Sys Quoted
Sys Quoted
Sys


I have tried to write a formula to count the number of times Row F has "Sys"
and Row G has "Ordered"

the formula I thought would work is

=(COUNTIF(F2:F48,"Sys"))*AND(COUNTIF(G2:G15,"Ordered"))

I always get 10 returned. The formula counts Row F but never takes into
account what is in Row G

I need the count to increment only if both conditions are true Row F = Sys
AND row G = Ordered

Any Ideas will be GREATLY APPRECIATED!

PS Why does Excel require a * in front of the AND statement?

I have tried many variations of this formula and it either doesn't work or
gives me 10 in return. I need it to return 2

Thanks In Advance!!!!!!!

Mark

The table got messed up a bit when posted.
The Row F is Job Type nad Row G is Order Status
I'm sure you get the idea what I am trying to do!

TIA


"Mark Stewart" <mark@afm.netNOSPAM> wrote in message
news:%23hB3JkFcGHA.3380@TK2MSFTNGP04.phx.gbl...
> Hi I am having a time with Countif statement. Here is what I have
>
> I have a table with some rows that I need to count
>
> Row F
> Job Type RowG
> Order Status
> Sys Ordered
> Prg Quoted
> Sys Quoted
> Prg Ordered
> Sys Ordered
> Prg Quoted
> Sys Quoted
> Prg Quoted
> Sys Quoted
> Sys
> Sys Quoted
> Sys Quoted
> Sys Quoted
> Sys
>
>
> I have tried to write a formula to count the number of times Row F has
> "Sys" and Row G has "Ordered"
>
> the formula I thought would work is
>
> =(COUNTIF(F2:F48,"Sys"))*AND(COUNTIF(G2:G15,"Ordered"))
>
> I always get 10 returned. The formula counts Row F but never takes into
> account what is in Row G
>
> I need the count to increment only if both conditions are true Row F = Sys
> AND row G = Ordered
>
> Any Ideas will be GREATLY APPRECIATED!
>
> PS Why does Excel require a * in front of the AND statement?
>
> I have tried many variations of this formula and it either doesn't work or
> gives me 10 in return. I need it to return 2
>
> Thanks In Advance!!!!!!!
>
> Mark
>
>
>

Don't you need to have the AND first in the formula? AND(Countif....
--
Life is an adventure, are you living it?

These are just my opinions, please feel free to correct them if they are
wrong.


"Mark Stewart" wrote:

> Hi I am having a time with Countif statement. Here is what I have
>
> I have a table with some rows that I need to count
>
> Row F
> Job Type RowG
> Order Status
> Sys Ordered
> Prg Quoted
> Sys Quoted
> Prg Ordered
> Sys Ordered
> Prg Quoted
> Sys Quoted
> Prg Quoted
> Sys Quoted
> Sys
> Sys Quoted
> Sys Quoted
> Sys Quoted
> Sys
>
>
> I have tried to write a formula to count the number of times Row F has "Sys"
> and Row G has "Ordered"
>
> the formula I thought would work is
>
> =(COUNTIF(F2:F48,"Sys"))*AND(COUNTIF(G2:G15,"Ordered"))
>
> I always get 10 returned. The formula counts Row F but never takes into
> account what is in Row G
>
> I need the count to increment only if both conditions are true Row F = Sys
> AND row G = Ordered
>
> Any Ideas will be GREATLY APPRECIATED!
>
> PS Why does Excel require a * in front of the AND statement?
>
> I have tried many variations of this formula and it either doesn't work or
> gives me 10 in return. I need it to return 2
>
> Thanks In Advance!!!!!!!
>
> Mark
>
>
>
>

Try something like this:

=SUMPRODUCT((F2:F48="Sys")*(G2:G48="Ordered"))

Both ranges *must* be of equal size (F2:F48 & G2:G48).
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Mark Stewart" <mark@afm.netNOSPAM> wrote in message
news:%23hB3JkFcGHA.3380@TK2MSFTNGP04.phx.gbl...
Hi I am having a time with Countif statement. Here is what I have

I have a table with some rows that I need to count

Row F
Job Type RowG
Order Status
Sys Ordered
Prg Quoted
Sys Quoted
Prg Ordered
Sys Ordered
Prg Quoted
Sys Quoted
Prg Quoted
Sys Quoted
Sys
Sys Quoted
Sys Quoted
Sys Quoted
Sys


I have tried to write a formula to count the number of times Row F has "Sys"
and Row G has "Ordered"

the formula I thought would work is

=(COUNTIF(F2:F48,"Sys"))*AND(COUNTIF(G2:G15,"Ordered"))

I always get 10 returned. The formula counts Row F but never takes into
account what is in Row G

I need the count to increment only if both conditions are true Row F = Sys
AND row G = Ordered

Any Ideas will be GREATLY APPRECIATED!

PS Why does Excel require a * in front of the AND statement?

I have tried many variations of this formula and it either doesn't work or
gives me 10 in return. I need it to return 2

Thanks In Advance!!!!!!!

Mark

Thanks RagDyer
I figured out that CountIf only works on one column at a time.

=SUMPRODUCT((F2:F48="Sys")*(G2:G48="Ordered"))
That worked.
I tried to use SumProduct and was real close but left out the = sign before
the "Sys" and "Ordered" and it gave me wrong answers!

Thanks again!


"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
news:uZjGLwFcGHA.3364@TK2MSFTNGP05.phx.gbl...
> Try something like this:
>
> =SUMPRODUCT((F2:F48="Sys")*(G2:G48="Ordered"))
>
> Both ranges *must* be of equal size (F2:F48 & G2:G48).
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "Mark Stewart" <mark@afm.netNOSPAM> wrote in message
> news:%23hB3JkFcGHA.3380@TK2MSFTNGP04.phx.gbl...
> Hi I am having a time with Countif statement. Here is what I have
>
> I have a table with some rows that I need to count
>
> Row F
> Job Type RowG
> Order Status
> Sys Ordered
> Prg Quoted
> Sys Quoted
> Prg Ordered
> Sys Ordered
> Prg Quoted
> Sys Quoted
> Prg Quoted
> Sys Quoted
> Sys
> Sys Quoted
> Sys Quoted
> Sys Quoted
> Sys
>
>
> I have tried to write a formula to count the number of times Row F has
> "Sys"
> and Row G has "Ordered"
>
> the formula I thought would work is
>
> =(COUNTIF(F2:F48,"Sys"))*AND(COUNTIF(G2:G15,"Ordered"))
>
> I always get 10 returned. The formula counts Row F but never takes into
> account what is in Row G
>
> I need the count to increment only if both conditions are true Row F = Sys
> AND row G = Ordered
>
> Any Ideas will be GREATLY APPRECIATED!
>
> PS Why does Excel require a * in front of the AND statement?
>
> I have tried many variations of this formula and it either doesn't work or
> gives me 10 in return. I need it to return 2
>
> Thanks In Advance!!!!!!!
>
> Mark
>
>
>
>