Counting with two criteria

[cwhiteheadjr84]

hey everyone i am trying to figure out the formula to allow me to count the number of times a word "open" appears if the text in column A matches the text in sheet 2 column A then counting the number of times "open" appears in sheet 2 column G

CLT5->DDU-CLT5-101 <colgroup><col width="148"></colgroup><tbody> </tbody>	2
CLT5->DDU-CLT5-102 <colgroup><col width="148"></colgroup><tbody> </tbody>	1
SHEET 1

<tbody>
</tbody>

CLT5->DDU-CLT5-101 <colgroup><col width="148"></colgroup><tbody> </tbody>	OPEN
CLT5->DDU-CLT5-101 <colgroup><col width="148"></colgroup><tbody> </tbody>	OPEN
CLT5->DDU-CLT5-101 <colgroup><col width="148"></colgroup><tbody> </tbody>	CLOSED
CLT5->DDU-CLT5-102 <colgroup><col width="148"></colgroup><tbody> </tbody>	OPEN
CLT5->DDU-CLT5-102 <colgroup><col width="148"></colgroup><tbody> </tbody>	CLOSED
SHEET 2

<tbody>
</tbody>

 

[AhoyNC]

Try:

Excel Workbook
	A	B
1	CLT5->DDU-CLT5-101	2
2	CLT5->DDU-CLT5-102	1
Sheet1

Excel Workbook
	A	B
1	CLT5->DDU-CLT5-101	OPEN
2	CLT5->DDU-CLT5-101	OPEN
3	CLT5->DDU-CLT5-101
4	CLT5->DDU-CLT5-102	OPEN
5	CLT5->DDU-CLT5-102	CLOSED
Sheet2

 

[cwhiteheadjr84]

Thank you that did the trick had the criteria backwards =COUNTIFS('VISTA DUMP'!A:A,A1,'VISTA DUMP'!G:G,"OPEN") now that i have this i need to build on to it to have it use these sames rules to get the sum of the numbers in VISTA DUMP COLUMN J that match these criteria.

Try:

Sheet1

	A	B
1	CLT5->DDU-CLT5-101	2
2	CLT5->DDU-CLT5-102	1

<colgroup><col style="width:30px; "><col style="width:143px;"><col style="width:107px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =COUNTIFS(Sheet2!$A$1:$A$5,A1,Sheet2!$B$1:$B$5,"OPEN") <tbody> </tbody>

<tbody>
</tbody>

Sheet2

	A	B
1	CLT5->DDU-CLT5-101	OPEN
2	CLT5->DDU-CLT5-101	OPEN
3	CLT5->DDU-CLT5-101
4	CLT5->DDU-CLT5-102	OPEN
5	CLT5->DDU-CLT5-102	CLOSED

<colgroup><col style="width:30px; "><col style="width:164px;"><col style="width:134px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AhoyNC]

Try SUMIFS functkion.