I have seen similar answers below but for some reason i can't get the other
approaches to work for my problem. I have a list of data in coumns A and B
which I need to count how many times certain combinations occur. For example:

6 M
6 M
7 M
8 E
9 M

Results: 6 and M - 2, 7 and M - 1, 8 and M - 0, 8 and M - 1 etc

I need excel to count the number of times that the cells have a combination
of say 6 and M or 8 and E for example. I have tried an AND with a countif
but without any success.

What is the simplest way to do this?

As a slight extension of this the numbers 6,7,8 etc come from a =month(a52)
formula for example. Ideally I would like a formula that looks does exactly
the same thing but counts the number of matches in June and M etc


1-Jun-07 M
2-Jun-07 M
10-Jun-07 M
12-Jun-07 E
2-Jul-07 M
12-Jul-07 M
12-Jul-07 M

In this case results would be-
June and M - 3, June and E - 1, July and M - 3

Is this getting to fancy or I am best to stick to extracting the month
sequence number and matching with that?

Thanks
LD

Try SUMPRODUCT():

=SUMPRODUCT((A1:A1000=6)*(B1:B1000="M"))

similar for any other combination
--
Gary''s Student - gsnu200817


"LiAD" wrote:

I have seen similar answers below but for some reason i can't get the other
approaches to work for my problem. I have a list of data in coumns A and B
which I need to count how many times certain combinations occur. For example:

6 M
6 M
7 M
8 E
9 M

Results: 6 and M - 2, 7 and M - 1, 8 and M - 0, 8 and M - 1 etc

I need excel to count the number of times that the cells have a combination
of say 6 and M or 8 and E for example. I have tried an AND with a countif
but without any success.

What is the simplest way to do this?

As a slight extension of this the numbers 6,7,8 etc come from a =month(a52)
formula for example. Ideally I would like a formula that looks does exactly
the same thing but counts the number of matches in June and M etc


1-Jun-07 M
2-Jun-07 M
10-Jun-07 M
12-Jun-07 E
2-Jul-07 M
12-Jul-07 M
12-Jul-07 M

In this case results would be-
June and M - 3, June and E - 1, July and M - 3

Is this getting to fancy or I am best to stick to extracting the month
sequence number and matching with that?

Thanks
LD

scenario 1 :

=COUNT(IF(($A$1:$A$15=6)*($B$1:$B$15="M"),)) ( use ctrl + shift +
enter )

scenario 2 :

=COUNT(IF((MONTH($A$1:$A$15)=6)*($B$1:$B$15="M"),) ) ( use ctrl + shift
+ enter )



On Dec 2, 9:41*pm, LiAD wrote:
I have seen similar answers below but for some reason i can't get the other
approaches to work for my problem. *I have a list of data in coumns A and B
which I need to count how many times certain combinations occur. *For example:

6 * M
6 * M *
7 * M
8 * E
9 * M

Results: *6 and M - 2, 7 and M - 1, 8 and M - 0, 8 and M - 1 etc

I need excel to count the number of times that the cells have a combination
of say 6 and M or 8 and E for example. *I have tried an AND with a countif
but without any success. *

What is the simplest way to do this?

As a slight extension of this the numbers 6,7,8 etc come from a =month(a52)
formula for example. *Ideally I would like a formula that looks does exactly
the same thing but counts the number of matches in June and M etc

1-Jun-07 * * M
2-Jun-07 * * M
10-Jun-07 * *M
12-Jun-07 * *E
2-Jul-07 * * M
12-Jul-07 * *M
12-Jul-07 * *M

In this case results would be-
June and M - 3, June and E - 1, July and M - 3

Is this getting to fancy or I am best to stick to extracting the month
sequence number and matching with that?

Thanks
LD

works perfect for both

thanks a million


"muddan madhu" wrote:

scenario 1 :

=COUNT(IF(($A$1:$A$15=6)*($B$1:$B$15="M"),)) ( use ctrl + shift +
enter )

scenario 2 :

=COUNT(IF((MONTH($A$1:$A$15)=6)*($B$1:$B$15="M"),) ) ( use ctrl + shift
+ enter )



On Dec 2, 9:41 pm, LiAD wrote:
I have seen similar answers below but for some reason i can't get the other
approaches to work for my problem. I have a list of data in coumns A and B
which I need to count how many times certain combinations occur. For example:

6 M
6 M
7 M
8 E
9 M

Results: 6 and M - 2, 7 and M - 1, 8 and M - 0, 8 and M - 1 etc

I need excel to count the number of times that the cells have a combination
of say 6 and M or 8 and E for example. I have tried an AND with a countif
but without any success.

What is the simplest way to do this?

As a slight extension of this the numbers 6,7,8 etc come from a =month(a52)
formula for example. Ideally I would like a formula that looks does exactly
the same thing but counts the number of matches in June and M etc

1-Jun-07 M
2-Jun-07 M
10-Jun-07 M
12-Jun-07 E
2-Jul-07 M
12-Jul-07 M
12-Jul-07 M

In this case results would be-
June and M - 3, June and E - 1, July and M - 3

Is this getting to fancy or I am best to stick to extracting the month
sequence number and matching with that?

Thanks
LD

Sorry but I have exactly the same question but for summing times instead of a
count function now. I have tried adapting it for a sum but again I cant
find how to use a sum or sumif with two conditions.

1-Jun-07 M 02:00
2-Jun-07 M 06:00
10-Jun-07 M 18:00
12-Jun-07 E 00:30
2-Jul-07 M 01:00
12-Jul-07 M 04:00
12-Jul-07 M 02:00

Results €“ Jun and M total time €“ 26 hours, Jun and E €“ 30 minutes, Jul and M
- 6 hours etc.

Do you know of a solution to this summing problem as well that is similar to
the formula you provided that sorts the month function as well?

Thanks for your help


"muddan madhu" wrote:

scenario 1 :

=COUNT(IF(($A$1:$A$15=6)*($B$1:$B$15="M"),)) ( use ctrl + shift +
enter )

scenario 2 :

=COUNT(IF((MONTH($A$1:$A$15)=6)*($B$1:$B$15="M"),) ) ( use ctrl + shift
+ enter )



On Dec 2, 9:41 pm, LiAD wrote:
I have seen similar answers below but for some reason i can't get the other
approaches to work for my problem. I have a list of data in coumns A and B
which I need to count how many times certain combinations occur. For example:

6 M
6 M
7 M
8 E
9 M

Results: 6 and M - 2, 7 and M - 1, 8 and M - 0, 8 and M - 1 etc

I need excel to count the number of times that the cells have a combination
of say 6 and M or 8 and E for example. I have tried an AND with a countif
but without any success.

What is the simplest way to do this?

As a slight extension of this the numbers 6,7,8 etc come from a =month(a52)
formula for example. Ideally I would like a formula that looks does exactly
the same thing but counts the number of matches in June and M etc

1-Jun-07 M
2-Jun-07 M
10-Jun-07 M
12-Jun-07 E
2-Jul-07 M
12-Jul-07 M
12-Jul-07 M

In this case results would be-
June and M - 3, June and E - 1, July and M - 3

Is this getting to fancy or I am best to stick to extracting the month
sequence number and matching with that?

Thanks
LD

LiAD,

=SUMPRODUCT((MONTH($A$1:$A$15)=6)*($B$1:$B$15="M") *$C$1:$C$15)

Format the cell for time...

Or a preferable solution: use a pivot table. Drag dates to row field area,
code letters to column field area, times to data area. Then group by month,
and apply formatting.

HTH,
Bernie
MS Excel MVP


"LiAD" wrote in message
...
Sorry but I have exactly the same question but for summing times instead
of a
count function now. I have tried adapting it for a sum but again I can't
find how to use a sum or sumif with two conditions.

1-Jun-07 M 02:00
2-Jun-07 M 06:00
10-Jun-07 M 18:00
12-Jun-07 E 00:30
2-Jul-07 M 01:00
12-Jul-07 M 04:00
12-Jul-07 M 02:00

Results - Jun and M total time - 26 hours, Jun and E - 30 minutes, Jul and
M
- 6 hours etc.

Do you know of a solution to this summing problem as well that is similar
to
the formula you provided that sorts the month function as well?

Thanks for your help


"muddan madhu" wrote:

scenario 1 :

=COUNT(IF(($A$1:$A$15=6)*($B$1:$B$15="M"),)) ( use ctrl + shift +
enter )

scenario 2 :

=COUNT(IF((MONTH($A$1:$A$15)=6)*($B$1:$B$15="M"),) ) ( use ctrl + shift
+ enter )



On Dec 2, 9:41 pm, LiAD wrote:
I have seen similar answers below but for some reason i can't get the
other
approaches to work for my problem. I have a list of data in coumns A
and B
which I need to count how many times certain combinations occur. For
example:

6 M
6 M
7 M
8 E
9 M

Results: 6 and M - 2, 7 and M - 1, 8 and M - 0, 8 and M - 1 etc

I need excel to count the number of times that the cells have a
combination
of say 6 and M or 8 and E for example. I have tried an AND with a
countif
but without any success.

What is the simplest way to do this?

As a slight extension of this the numbers 6,7,8 etc come from a
=month(a52)
formula for example. Ideally I would like a formula that looks does
exactly
the same thing but counts the number of matches in June and M etc

1-Jun-07 M
2-Jun-07 M
10-Jun-07 M
12-Jun-07 E
2-Jul-07 M
12-Jul-07 M
12-Jul-07 M

In this case results would be-
June and M - 3, June and E - 1, July and M - 3

Is this getting to fancy or I am best to stick to extracting the month
sequence number and matching with that?

Thanks
LD