|
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,"Order ed")) 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 |
CountIf trouble
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" wrote in message ... 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,"Order ed")) 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 |
CountIf trouble
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,"Order ed")) 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 |
CountIf trouble
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" wrote in message ... 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,"Order ed")) 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 |
CountIf trouble
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" wrote in message ... 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" wrote in message ... 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,"Order ed")) 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 |
| All times are GMT +1. The time now is 10:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com