Double CountIF

L

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
 
G

Gary''s Student

Try SUMPRODUCT():

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

similar for any other combination
 
M

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 )
 
L

LiAD

works perfect for both

thanks a million


muddan madhu said:
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 )
 
L

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top