Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refining Countif
I am using Countif as per this example =COUNTIF(Sheet2!$G$1:$G$30000,
"=redcat") . I would like to find only the redcat11 's only in one zip code which is in column k. I have tried sumproduct and sum but they dont work. There is on numerical value for redcat just the value redcat. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refining Countif
=sumproduct(--(sheet2!$g$1:$g$30000="Redcat"),--(sheet2!$k$1:$k$30000=12345))
=sumproduct() likes to work with numbers. the -- stuff converts trues and falses to 1's and 0's. If the zip codes are text, use ="12345". And you can't use the whole column in this kind of formula. asg2307 wrote: I am using Countif as per this example =COUNTIF(Sheet2!$G$1:$G$30000, "=redcat") . I would like to find only the redcat11 's only in one zip code which is in column k. I have tried sumproduct and sum but they dont work. There is on numerical value for redcat just the value redcat. Thanks. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refining Countif
And I'm not sure how redcat11 fit in??
Dave Peterson wrote: =sumproduct(--(sheet2!$g$1:$g$30000="Redcat"),--(sheet2!$k$1:$k$30000=12345)) =sumproduct() likes to work with numbers. the -- stuff converts trues and falses to 1's and 0's. If the zip codes are text, use ="12345". And you can't use the whole column in this kind of formula. asg2307 wrote: I am using Countif as per this example =COUNTIF(Sheet2!$G$1:$G$30000, "=redcat") . I would like to find only the redcat11 's only in one zip code which is in column k. I have tried sumproduct and sum but they dont work. There is on numerical value for redcat just the value redcat. Thanks. -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refining Countif
redcat11 was a mistake.
"Dave Peterson" wrote: And I'm not sure how redcat11 fit in?? Dave Peterson wrote: =sumproduct(--(sheet2!$g$1:$g$30000="Redcat"),--(sheet2!$k$1:$k$30000=12345)) =sumproduct() likes to work with numbers. the -- stuff converts trues and falses to 1's and 0's. If the zip codes are text, use ="12345". And you can't use the whole column in this kind of formula. asg2307 wrote: I am using Countif as per this example =COUNTIF(Sheet2!$G$1:$G$30000, "=redcat") . I would like to find only the redcat11 's only in one zip code which is in column k. I have tried sumproduct and sum but they dont work. There is on numerical value for redcat just the value redcat. Thanks. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refining Countif
This worked great. I ended up with a negative number, but resolved this by
putting a minus sign before sumproduct. Maybe if you have time you can tell me how to properly write the formula. Thanks!! Alan "Dave Peterson" wrote: =sumproduct(--(sheet2!$g$1:$g$30000="Redcat"),--(sheet2!$k$1:$k$30000=12345)) =sumproduct() likes to work with numbers. the -- stuff converts trues and falses to 1's and 0's. If the zip codes are text, use ="12345". And you can't use the whole column in this kind of formula. asg2307 wrote: I am using Countif as per this example =COUNTIF(Sheet2!$G$1:$G$30000, "=redcat") . I would like to find only the redcat11 's only in one zip code which is in column k. I have tried sumproduct and sum but they dont work. There is on numerical value for redcat just the value redcat. Thanks. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Refining Countif
Disregard the adding the negative sign comment. My error.
"Dave Peterson" wrote: And I'm not sure how redcat11 fit in?? Dave Peterson wrote: =sumproduct(--(sheet2!$g$1:$g$30000="Redcat"),--(sheet2!$k$1:$k$30000=12345)) =sumproduct() likes to work with numbers. the -- stuff converts trues and falses to 1's and 0's. If the zip codes are text, use ="12345". And you can't use the whole column in this kind of formula. asg2307 wrote: I am using Countif as per this example =COUNTIF(Sheet2!$G$1:$G$30000, "=redcat") . I would like to find only the redcat11 's only in one zip code which is in column k. I have tried sumproduct and sum but they dont work. There is on numerical value for redcat just the value redcat. Thanks. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Formula /Sort Bug??? | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
Countif - Countif | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions |