count occurrences with two criteria

[lynnrudolph]

I have some simple text data (5 columns that include disease and country), say A-E and 1000 rows, where cols B & C are disease and country, respectively. I need to count how many occurrences of TB are in Chad for example, in a separate cell. Any help would be appreciated. Thank you! Oh and it must be compatible with Excel 97 as I am using an ancient version at the moment.

 

[shadow12345]

Sounds like you want a pivot table. They aren't to complex but a bit tricky to explain in a simple way.

This has a guide.
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

But as a rough idea these are the steps. (I don't have 97 so from memory im afraid)

Highlight all of the data you want to pivot.
press insert on the menu bar, then pivot table
Press use current selection and then next
press wizard (if its not already open)
Drag the column name on the left onto the field map on the right (in your case Col B on the Row & col C on the Column. Then put Col B in the data)

Press finish.

Its just a rough idea but it should get you start. Maybe someone has 97 and can be more acurate with some instructions.

 

[Aladin Akyurek]

I have some simple text data (5 columns that include disease and country), say A-E and 1000 rows, where cols B & C are disease and country, respectively. I need to count how many occurrences of TB are in Chad for example, in a separate cell. Any help would be appreciated. Thank you! Oh and it must be compatible with Excel 97 as I am using an ancient version at the moment.

Try...

K2: TB

L2: Chad

M2:

=SUMPRODUCT(--($B$2:$B$1000=K2),--($C$2:$C$2000=L2))

 

[Robert Mika]

Try PivotTable

 

[lynnrudolph]

I hope this reply works...not sure if I'm doing it right. Unfortunately, I can't do a pivot table because I'm actually not doing this in Excel 97, but Excel 2010, then saving backwards to 97 version so it can be used by my friend in another country. And I'm not good enough in 2010 to do a proper pivot table that I can be sure will work in the earlier version. So I was looking for just a formula. Sorry, I should have stated that in the original question. Otherwise, a pivot table would have been perfect.

 

[Aladin Akyurek]

I hope this reply works...not sure if I'm doing it right. Unfortunately, I can't do a pivot table because I'm actually not doing this in Excel 97, but Excel 2010, then saving backwards to 97 version so it can be used by my friend in another country. And I'm not good enough in 2010 to do a proper pivot table that I can be sure will work in the earlier version. So I was looking for just a formula. Sorry, I should have stated that in the original question. Otherwise, a pivot table would have been perfect.

Post #3 offers you a formula...