Count unique/total entries based on multiple criteria

[RodneyW]

C3:C1000 has various cities listed, most are listed more than once for example, Atlanta, Chicago, New York and so on.

E3:E1000 has various colors listed, most more than once.

Atlanta may be listed 200 times in column C, of those Blue may be listed 25 times in column E and Green may be listed 41 times.
Chicago may be listed 250 time in column C, of those Blue may be listed 12 times in column E and Green may be listed 24 times.

If I just count how many times Blue is listed, I'd get 37. If I count how many times Green is listed, I'd get 65. This isn't what I need.

I need a formula in column L that will count Atlanta as 1, blue as 25 -OR- Atlanta as 1, green as 41 and so on.

Thanks in advance

 

[duggie33]

Try this...

Book1
	A	B	C	D	E	F
1	City	Color
2	Atlanta	Blue		Atlanta	Blue	4
3	Atlanta	Blue		Atlanta	Green	4
4	Chicago	Green		Chicago	Blue	5
5	Chicago	Blue		Chicago	Green	6
6	Chicago	Blue
7	Chicago	Blue
8	Atlanta	Green
9	Chicago	Blue
10	Chicago	Green
11	Atlanta	Green
12	Atlanta	Green
13	Chicago	Green
14	Atlanta	Blue
15	Atlanta	Blue
16	Chicago	Blue
17	Chicago	Green
18	Chicago	Green
19	Atlanta	Green
20	Chicago	Green
Sheet3

Cell Formulas
Range	Formula
F2:F5	F2	=COUNTIFS($A$2:$A$20,D2,$B$2:$B$20,E2)

Hope that helps,

Doug

 

[RodneyW]

Try this...

Book1
	A	B	C	D	E	F
1	City	Color
2	Atlanta	Blue		Atlanta	Blue	4
3	Atlanta	Blue		Atlanta	Green	4
4	Chicago	Green		Chicago	Blue	5
5	Chicago	Blue		Chicago	Green	6
6	Chicago	Blue
7	Chicago	Blue
8	Atlanta	Green
9	Chicago	Blue
10	Chicago	Green
11	Atlanta	Green
12	Atlanta	Green
13	Chicago	Green
14	Atlanta	Blue
15	Atlanta	Blue
16	Chicago	Blue
17	Chicago	Green
18	Chicago	Green
19	Atlanta	Green
20	Chicago	Green
Sheet3

Cell Formulas
Range	Formula
F2:F5	F2	=COUNTIFS($A$2:$A$20,D2,$B$2:$B$20,E2)

Hope that helps,

Doug

That worked, thank you

 

[duggie33]

You're welcome. Glad to help.

Doug