Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif with double criteria
I'm trying to find a formula to count cells if they respond to 2 criteria.
For example I have a list of transactions which are coded by a list of 2 criteria. The first list of code is : E, T, M, S, O and a second list of codes is: 1, 2, 3. Ex: Transaction 1 E 2 Transaction 2 T 3 Transaction 3 S 1 Transaction 4 M 2 etc. I want to count the number of transactions which are both equal to E and 2 at the same time. Thanks ! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif with double criteria
Hi Benoit
Use SUMPRODUCT: =SUMPRODUCT(--(B2:B1000="E"),--(C2:C1000=2)) Note that I have assumed that codes E,T etc are in col B and codes 1,2,3 are in col C. Note also you can't use whole column refs, so you will need to adjust ranges to suit (can't use B:B for example, but B2:B50000 is OK). Hope this helps! Richard Benoit wrote: I'm trying to find a formula to count cells if they respond to 2 criteria. For example I have a list of transactions which are coded by a list of 2 criteria. The first list of code is : E, T, M, S, O and a second list of codes is: 1, 2, 3. Ex: Transaction 1 E 2 Transaction 2 T 3 Transaction 3 S 1 Transaction 4 M 2 etc. I want to count the number of transactions which are both equal to E and 2 at the same time. Thanks ! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif with double criteria
Thanks. I have one more related question. If I also want to use these same
criteria to add amounts, what formula would I use? For example: Colum A B C D Transaction 1 E 2 5122.00$ Transaction 2 T 3 1230.25$ Transaction 3 S 1 135.36$ Transaction 4 M 2 142.57$ etc. I want to add $ of transactions which are both equal to E and 2 at the same time. Thanks again ! "RichardSchollar" wrote: Hi Benoit Use SUMPRODUCT: =SUMPRODUCT(--(B2:B1000="E"),--(C2:C1000=2)) Note that I have assumed that codes E,T etc are in col B and codes 1,2,3 are in col C. Note also you can't use whole column refs, so you will need to adjust ranges to suit (can't use B:B for example, but B2:B50000 is OK). Hope this helps! Richard Benoit wrote: I'm trying to find a formula to count cells if they respond to 2 criteria. For example I have a list of transactions which are coded by a list of 2 criteria. The first list of code is : E, T, M, S, O and a second list of codes is: 1, 2, 3. Ex: Transaction 1 E 2 Transaction 2 T 3 Transaction 3 S 1 Transaction 4 M 2 etc. I want to count the number of transactions which are both equal to E and 2 at the same time. Thanks ! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif with double criteria
try this
=SUMPRODUCT(D2:D5,--(B2:B5="E"),--(C2:C5=2)) "Benoit" wrote in message ... Thanks. I have one more related question. If I also want to use these same criteria to add amounts, what formula would I use? For example: Colum A B C D Transaction 1 E 2 5122.00$ Transaction 2 T 3 1230.25$ Transaction 3 S 1 135.36$ Transaction 4 M 2 142.57$ etc. I want to add $ of transactions which are both equal to E and 2 at the same time. Thanks again ! "RichardSchollar" wrote: Hi Benoit Use SUMPRODUCT: =SUMPRODUCT(--(B2:B1000="E"),--(C2:C1000=2)) Note that I have assumed that codes E,T etc are in col B and codes 1,2,3 are in col C. Note also you can't use whole column refs, so you will need to adjust ranges to suit (can't use B:B for example, but B2:B50000 is OK). Hope this helps! Richard Benoit wrote: I'm trying to find a formula to count cells if they respond to 2 criteria. For example I have a list of transactions which are coded by a list of 2 criteria. The first list of code is : E, T, M, S, O and a second list of codes is: 1, 2, 3. Ex: Transaction 1 E 2 Transaction 2 T 3 Transaction 3 S 1 Transaction 4 M 2 etc. I want to count the number of transactions which are both equal to E and 2 at the same time. Thanks ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel COUNTIF Function - a range as criteria ? | Excel Worksheet Functions | |||
Countif with more than one criteria | Excel Worksheet Functions | |||
Countif with star operator in criteria | Excel Worksheet Functions | |||
COUNTIF or SUMPRODUCT counting multiple criteria | Excel Worksheet Functions | |||
countif variable criteria | Excel Discussion (Misc queries) |