Counting based on multiple criteria

[bbluechipp]

Hi,

I would be grateful if someone can help me with this. Please see th
attached picture, if you can. Otherwise, this is the description of m
spreadsheet

I have 4 column A, B, C,D. A1&B1 is merged as a header row says 8-9A
while C1&D1 is merged as 9-10AM

In A and C column, we have project code CA03, CA04
In B and D column, we have activity code ON/OF or TR

A B C D
1 8-9AM 9-10AM
2 CA03 ON CA03 ON
3 CA03 ON CA03 TR
4 CA03 TR CA04 OF
5 CA04 OF CA03 OF

What I want is to count in range (A25):
- How many CA03 and TR (together). The result should be 2
- How many CA 03 and ON/OF. The result should be 4.

I've tried with countif, count(if etc. but haven't got the righ
results yet.

Thanks in advance for your help.

Best regards,
Thu

Attachment filename: example - counting.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=60167

 

[Guest]

=SUMPRODUCT((A1:A4="CA03")*(B1:B4="TR"))+SUMPRODUCT((C1:C4="CA03")*(D14="TR"))

=SUMPRODUCT((A1:A4=A1)*(B1:B4={"ON","OF"}))+SUMPRODUCT((C1:C4=A1)*(D14={"ON","OF"}))