Double CountIF

[LiAD]

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

 

[Gary''s Student]

Try SUMPRODUCT():

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

similar for any other combination

 

[muddan madhu]

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 )

 

[LiAD]

works perfect for both

thanks a million

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 )

 

[LiAD]

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

 

[Bernie Deitrick]

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