Counting within a range meeting a range of criteria

[bgill]

I have imported data from a .prn file and need to count a few different combinations. 1 - I need to count the number of times the number in column A is within a certain range (ie. 500-550). Sometimes the number is on the list more than once but I can only count it one time.
2 - I need to know how many times speciic text shows up in column B related to the range in column A.
Specifically, how many "GNNK"s or "GNND"s (column B) are in the 500 range (column A) or the 600 range or the 700 range, etc.

Here is a short sample of the list:
A B
525 GNNK
528 GNNK
530 GNND
531 GNNK
532 GNNK
532 GNNK
533 GNND
534 GNNK
535 GNND
537 GNNK
539 GNNK
601 GNND
601 GNND
603 GNNK
605 GNNK
607 GNNK
608 GNND
610 GNNK

Please let me know if I can clarify this question. Thank you.

 

[Brian from Maui]

Something like this

Book3
	A	B	C	D
1	gnnk	501		2
2	gnnk	601
3	gnnd	701
4	gnnd	801
5	gnnd	800
Sheet1

 

[bgill]

Thank you Brian. I may have entered something wrong but the formula does not seem to be related to the number in column B. It counts the number of times the text appears in column A without regard to the number in column B being <="600". Do you have any other suggestions?

 

[Aladin Akyurek]

On 2002-08-23 15:59, bgill wrote:
Thank you Brian. I may have entered something wrong but the formula does not seem to be related to the number in column B. It counts the number of times the text appears in column A without regard to the number in column B being <="600". Do you have any other suggestions?

Becky,

Remove the double quotes around 600.

Aladin

 

[Mark W.]

Book2.xls
	A	B	C	D	E	F	G	H
1	Field1	Field2
2	525	GNNK
3	528	GNNK
4	530	GNND
5	531	GNNK
6	532	GNNK
7	532	GNNK
8	533	GNND		CountofField2	Field2
9	534	GNNK		Field1	GNND	GNNK	GrandTotal
10	535	GNND		500-599	3	8	11
11	537	GNNK		600-699	3	5	8
12	539	GNNK		GrandTotal	6	13	19
13	601	GNND
14	601	GNND
15	603	GNNK
16	605	GNNK
17	607	GNNK
18	608	GNND
19	610	GNNK
20	610	GNNK
21
Sheet2

 

[bgill]

Getting closer! Thank you Aladin - your tip worked to get things moving in the right direction.

Mark, your table is PERFECT! I can't see the formulas you entered. Is there any way that you can get them to me?

 

[Mark W.]

Becky, there aren't any formula's in my solution. It employes a PivotTable.