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 !

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 !

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 !

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 !