Counting Values based on other columns...HELP!

[jambr124]

Good Morning Guys,

I am working on a formula and STUCK!The problem is I have a sheet with data split into 5 columns. (See Data Table Below) Basically it needs to be: If Column 1 is a unique value (don't count duplicates) and if Column 2 is "United States", how many does each entry in column 3 occur. For example, the end result should look like this:

End Result

<colgroup><col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> <col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> </colgroup><tbody>
[TD="class: xl68, width: 90"] United States
[/TD]
[TD="class: xl67, width: 76, align: center"] 10
[/TD]

[TD="class: xl66, align: right"]North
[/TD]
[TD="class: xl65, align: center"]2[/TD]

[TD="class: xl66, align: right"]South[/TD]
[TD="class: xl65, align: center"]3[/TD]

[TD="class: xl66, align: right"]East[/TD]
[TD="class: xl65, align: center"]3
[/TD]

[TD="class: xl66, align: right"]West
[/TD]
[TD="class: xl65, align: center"]2[/TD]

[TD="class: xl68"]Canada (All)
[/TD]
[TD="class: xl67, align: center"]5
[/TD]

</tbody>

Data Table
[TABLE="width: 514"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Column 1
[/TD]
[TD]Column 2
[/TD]
[TD]Column 3
[/TD]
[TD]Column 4
[/TD]
[TD]Column 5
[/TD]
[/TR]
[TR]
[TD]A11[/TD]
[TD]United States[/TD]
[TD]North[/TD]
[TD]Deer[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A11[/TD]
[TD]United States[/TD]
[TD]North[/TD]
[TD]Duck[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]A14[/TD]
[TD]Canada[/TD]
[TD]West[/TD]
[TD]Elk[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A21[/TD]
[TD]Canada[/TD]
[TD]West[/TD]
[TD]Deer[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]A21[/TD]
[TD]Canada[/TD]
[TD]West[/TD]
[TD]Duck[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]A22[/TD]
[TD]United States[/TD]
[TD]West[/TD]
[TD]Bear[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]A22[/TD]
[TD]United States[/TD]
[TD]West[/TD]
[TD]Fox[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A22[/TD]
[TD]United States[/TD]
[TD]West[/TD]
[TD]Duck[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]A23[/TD]
[TD]United States[/TD]
[TD]south[/TD]
[TD]Pelican[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A24[/TD]
[TD]United States[/TD]
[TD]south[/TD]
[TD]Dolphin[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A32[/TD]
[TD]United States[/TD]
[TD]east[/TD]
[TD]Pelican[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A32[/TD]
[TD]United States[/TD]
[TD]east[/TD]
[TD]duck[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A32[/TD]
[TD]United States[/TD]
[TD]east[/TD]
[TD]Elk[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A32[/TD]
[TD]United States[/TD]
[TD]east[/TD]
[TD]Deer[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]A32[/TD]
[TD]United States[/TD]
[TD]east[/TD]
[TD]Bear[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]A43[/TD]
[TD]Canada[/TD]
[TD]east[/TD]
[TD]bear[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]A43[/TD]
[TD]Canada[/TD]
[TD]east[/TD]
[TD]elk[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]A43[/TD]
[TD]Canada[/TD]
[TD]east[/TD]
[TD]Deer[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A43[/TD]
[TD]Canada[/TD]
[TD]east[/TD]
[TD]duck[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A43[/TD]
[TD]Canada[/TD]
[TD]east[/TD]
[TD]fox[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]A53[/TD]
[TD]Canada[/TD]
[TD]south[/TD]
[TD]fox[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]A54[/TD]
[TD]United States[/TD]
[TD]south[/TD]
[TD]fox[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]A55[/TD]
[TD]United States[/TD]
[TD]North[/TD]
[TD]elk[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A55[/TD]
[TD]United States[/TD]
[TD]North[/TD]
[TD]deer[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A55[/TD]
[TD]United States[/TD]
[TD]North[/TD]
[TD]duck[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A56[/TD]
[TD]United States[/TD]
[TD]east[/TD]
[TD]fox[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A57
[/TD]
[TD]United States[/TD]
[TD]east[/TD]
[TD]duck[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A67[/TD]
[TD]United States[/TD]
[TD]west[/TD]
[TD]deer[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A67[/TD]
[TD]United States[/TD]
[TD]West[/TD]
[TD]fox[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A98[/TD]
[TD]Canada[/TD]
[TD]north[/TD]
[TD]elk[/TD]
[TD]56[/TD]
[/TR]
</tbody>[/TABLE]

 

[jambr124]

to help clarify...I need to count how many times a value occurs in Column 3, based on Columns A and B. The formula say something like: if Column 1 is a unique value (don't count duplicates) and if Column 2 is "United States", how many times does each entry in column 3 occur (North, South, East or West).

 

[Aladin Akyurek]

How did you get 3 foe United States X South?

 

[jambr124]

How did you get 3 foe United States X South?

I removed the duplicates, filtered by US, then filtered by south...it brought up ID NUMBERS A23, A24, and A54.

 

[Aladin Akyurek]

I removed the duplicates, filtered by US, then filtered by south...it brought up ID NUMBERS A23, A24, and A54.

A description that makes sense. Thanks.

Let A:E house the data, with heders in the first row.

G1 houses United States, G2:G5 North, South, East, and West.

H2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$31<>"",
  IF($B$2:$B$31=$G$1,IF($C$2:$C$31=$G2,
  MATCH("~"&$A$2:$A$31,$A$2:$A$31&"",0)))),
  ROW($A$2:$A$31)-ROW($A$2)+1),1))

 

[jambr124]

It worked!...Thank you very much!

 

[Aladin Akyurek]

It worked!...Thank you very much!

You are welcome. Thanks for providing feedback.