using COUNTIF for two column

[zaidan]

dear all,

In sheet1, i have "METAR code" in column A and "insert time" in column B, i want to count data with two categories by combine parse of metar code, i.e "METAR WAAA" and "insert time". FYI, i want to put The summaries is in sheet2.

example :
Data :

Excel Workbook
	A	B
1	metar_code	insert_time
2	METAR WAAA 010000Z 23008KT 180V300 1000 RA FEW017CB BKN018 24/23 Q1010	01/03/2011 0:00
3	METAR WAAA 010030Z 06004KT 6000 R03/2000 -RA FEW017CB BKN018 24/24 Q1010	01/03/2011 0:30
4	METAR WAAA 010100Z 06005KT 6000 -RA BKN017 24/24 Q1010=	01/03/2011 1:00
5	METAR WAAA 010130Z 08006KT 7000 -RA BKN019 25/25 Q1010=	01/03/2011 1:30
Sheet1

result i want :

Excel Workbook
	A	B	C	D	E
1		00:00	00:30	01:00	01:30
2	01/01/2011	1	1	1	1
Sheet2

thanks for any help.

 

[Joseph.Marro]

Zaidan,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I assume you are matching "METAR WAAA" in sheet1 column “A”, the date in column “A” of sheet2 to the date in column “B” In sheet1, and the times in row 1 in sheet2 to the times in column “B” of sheet1. If so, this formula will conduct a count based on these three criteria.<o></o>
<o></o>
=SUMPRODUCT(--(ISNUMBER(SEARCH("METAR WAAA",Sheet1!$A$2:$A$5,1))),--(TEXT(Sheet1!$B$2:$B$5,"hh:mm:ss am/pm")=TEXT(B$1,"hh:mm:ss am/pm")),--(TEXT(Sheet1!$B$2:$B$5,"mm/dd/yyyy")=TEXT($A2,"mm/dd/yyyy")))<o></o>
<o></o>
If I have assumed incorrectly please let me know and I can modify the formula.<o></o>
<o></o>
Thank you,<o></o>
<o></o>
Joseph Marro<o></o>
<o> </o>