CountIf trouble

[Mark Stewart]

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

 

[Mark Stewart]

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

 

[Guest]

Don't you need to have the AND first in the formula? AND(Countif....

 

[RagDyeR]

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

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

 

[Mark Stewart]

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!