| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
is there a way to have 2 criterias for COUNTIF?
Please help me with following!!
A B APPLE FRUIT PEACH FRUIT APPLE FRUIT APPLE NO FRUIT PEACH NO FRUIT I have above list and i would like to get a cell count for "APPLE" WITH "FRUIT" "APPLE" WITH "NO FRUIT" "PEACH" WITH "FRUIT" "PEACH" WITH "NO FRUIT" i prefer not using pivot or dcounta thank you!!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a way to have 2 criterias for COUNTIF?
=SUMPRODUCT(--(A1:A5="APPLE"),--(B1:B5="FRUIT"))
Adjust for other criteria. HTH, Paul -- "doyree" wrote in message ... Please help me with following!! A B APPLE FRUIT PEACH FRUIT APPLE FRUIT APPLE NO FRUIT PEACH NO FRUIT I have above list and i would like to get a cell count for "APPLE" WITH "FRUIT" "APPLE" WITH "NO FRUIT" "PEACH" WITH "FRUIT" "PEACH" WITH "NO FRUIT" i prefer not using pivot or dcounta thank you!!! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a way to have 2 criterias for COUNTIF?
Try, for example =SUMPRODUCT(--(A1:A10="Apple"),--(B1:B10="Fruit")) Answer
2. Adjust formula for other combinations. Tyro "doyree" wrote in message ... Please help me with following!! A B APPLE FRUIT PEACH FRUIT APPLE FRUIT APPLE NO FRUIT PEACH NO FRUIT I have above list and i would like to get a cell count for "APPLE" WITH "FRUIT" "APPLE" WITH "NO FRUIT" "PEACH" WITH "FRUIT" "PEACH" WITH "NO FRUIT" i prefer not using pivot or dcounta thank you!!! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a way to have 2 criterias for COUNTIF?
Try the following:
=SUMPRODUCT((A2:A6="Apple")*(B2:B6="Fruit")) Laura "doyree" wrote in message ... Please help me with following!! A B APPLE FRUIT PEACH FRUIT APPLE FRUIT APPLE NO FRUIT PEACH NO FRUIT I have above list and i would like to get a cell count for "APPLE" WITH "FRUIT" "APPLE" WITH "NO FRUIT" "PEACH" WITH "FRUIT" "PEACH" WITH "NO FRUIT" i prefer not using pivot or dcounta thank you!!! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a way to have 2 criterias for COUNTIF?
THANK YOU THANK YOU THANK YOU!!!!!!!!!!!!!!!!!!!!!! what's "--" in that formula mean? thanks "PCLIVE" wrote: =SUMPRODUCT(--(A1:A5="APPLE"),--(B1:B5="FRUIT")) Adjust for other criteria. HTH, Paul -- "doyree" wrote in message ... Please help me with following!! A B APPLE FRUIT PEACH FRUIT APPLE FRUIT APPLE NO FRUIT PEACH NO FRUIT I have above list and i would like to get a cell count for "APPLE" WITH "FRUIT" "APPLE" WITH "NO FRUIT" "PEACH" WITH "FRUIT" "PEACH" WITH "NO FRUIT" i prefer not using pivot or dcounta thank you!!! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a way to have 2 criterias for COUNTIF?
Sorry to bother you but is there way to put a cell in place of "apple"?
say... C4 in place of "apple"? thank you!! "PCLIVE" wrote: =SUMPRODUCT(--(A1:A5="APPLE"),--(B1:B5="FRUIT")) Adjust for other criteria. HTH, Paul -- "doyree" wrote in message ... Please help me with following!! A B APPLE FRUIT PEACH FRUIT APPLE FRUIT APPLE NO FRUIT PEACH NO FRUIT I have above list and i would like to get a cell count for "APPLE" WITH "FRUIT" "APPLE" WITH "NO FRUIT" "PEACH" WITH "FRUIT" "PEACH" WITH "NO FRUIT" i prefer not using pivot or dcounta thank you!!! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a way to have 2 criterias for COUNTIF?
The "--" coerces the conversion of the logical values TRUE and FALSE to be
1 and 0. One minus makes TRUE = -1 and FALSE = 0. The second minus reverses the signs, so TRUE becomes 1 and FALSE becomes 0. Then the numbers are multiplied and summed. For every "Apple" in A1:A5 there will be a 1 and for every "Fruit" in B1:B5 there will be a 1. So apple and fruit in the same row results in 1 * 1, and all other combinations result in 0. Tyro "doyree" wrote in message ... THANK YOU THANK YOU THANK YOU!!!!!!!!!!!!!!!!!!!!!! what's "--" in that formula mean? thanks "PCLIVE" wrote: =SUMPRODUCT(--(A1:A5="APPLE"),--(B1:B5="FRUIT")) Adjust for other criteria. HTH, Paul -- "doyree" wrote in message ... Please help me with following!! A B APPLE FRUIT PEACH FRUIT APPLE FRUIT APPLE NO FRUIT PEACH NO FRUIT I have above list and i would like to get a cell count for "APPLE" WITH "FRUIT" "APPLE" WITH "NO FRUIT" "PEACH" WITH "FRUIT" "PEACH" WITH "NO FRUIT" i prefer not using pivot or dcounta thank you!!! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is there a way to have 2 criterias for COUNTIF?
Yep. Put C4 in place of apple.
Tyro "doyree" wrote in message ... Sorry to bother you but is there way to put a cell in place of "apple"? say... C4 in place of "apple"? thank you!! "PCLIVE" wrote: =SUMPRODUCT(--(A1:A5="APPLE"),--(B1:B5="FRUIT")) Adjust for other criteria. HTH, Paul -- "doyree" wrote in message ... Please help me with following!! A B APPLE FRUIT PEACH FRUIT APPLE FRUIT APPLE NO FRUIT PEACH NO FRUIT I have above list and i would like to get a cell count for "APPLE" WITH "FRUIT" "APPLE" WITH "NO FRUIT" "PEACH" WITH "FRUIT" "PEACH" WITH "NO FRUIT" i prefer not using pivot or dcounta thank you!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif with two criterias | Excel Discussion (Misc queries) | |||
| 2 criterias for countif - | Excel Worksheet Functions | |||
| Countif, two criterias need help | Excel Worksheet Functions | |||
| Formula format for Count or Countif funtion with two criterias | Excel Worksheet Functions | |||
| countif help for multiple criterias | Excel Worksheet Functions | |||
Linear Mode
