Vlookup, Match, Count

[camle]

I am looking for a way to look in a table or list and count if they match.
Ex.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]American Pickers
[/TD]
[TD]2011x01
[/TD]
[TD]2011
[/TD]
[/TR]
[TR]
[TD]American Pickers
[/TD]
[TD]2011x02
[/TD]
[TD]2011
[/TD]
[/TR]
[TR]
[TD]American Pickers
[/TD]
[TD]2012x01
[/TD]
[TD]2012
[/TD]
[/TR]
[TR]
[TD]American Pickers
[/TD]
[TD]2012x02
[/TD]
[TD]2012
[/TD]
[/TR]
[TR]
[TD]American Pickers
[/TD]
[TD]2012x03
[/TD]
[TD]2012
[/TD]
[/TR]
[TR]
[TD]Bones
[/TD]
[TD]9x01
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Bones
[/TD]
[TD]9x02
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Bones
[/TD]
[TD]9x02
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Bones
[/TD]
[TD]9x02
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Bones
[/TD]
[TD]10x01
[/TD]
[TD]10
[/TD]
[/TR]
</tbody>[/TABLE]

Output should be:
American Pickers
2011=2
2012=3

Bones:
9=4
10=1

Hope I explain well enough.
Thanks

 

[shahfahad]

Try this. Its working for me.

=COUNTIFS(Sheet2!$B$2:$B$11,Sheet1!B$2,Sheet2!$D$2:$D$11,Sheet1!B6)

Sheet 1 is American Picker Tab and Sheet 2 is Get Files Tab. Hope this helps.

 

[Oeldere]

@shahfahad

the syntax of the formula looks wrong to me.

=VLOOKUP(Workbook1,$A$1:$C$10!Worbook2,3,0)

 

[camle]

Try this. Its working for me.

=COUNTIFS(Sheet2!$B$2:$B$11,Sheet1!B$2,Sheet2!$D$2:$D$11,Sheet1!B6)

Sheet 1 is American Picker Tab and Sheet 2 is Get Files Tab. Hope this helps.

Looks promising,
I didn't understand the Sheet2!$B$2:$B$11 Sheet2!$D$2:$D$11
I did GetFiles!A1:A122 GetFiles!C1:C122

1st range is looking for American Picker in the 1st column
2nd range is looking for Season (2010) in the 3rd column
Then the 1st Criteria "2010"
2nd criteria "22"
Right?

Why do i get "0"

I tried to post a picture of the formula to show what it is doing but it won't let me.



Thanks

 

[camle]

@shahfahad

the syntax of the formula looks wrong to me.

=VLOOKUP(Workbook1,$A$1:$C$10!Worbook2,3,0)

For some reason I can't get this one to work, I get errors

Also how does this formula count?

I'm a lightweight in the excel world?
Want to learn as mush as possible

Thanks

 

[camle]

For some reason I can't get this one to work, I get errors

Also how does this formula count?

I'm a lightweight in the excel world?
Want to learn as mush as possible

Thanks

Never mind I see what I did.

=COUNTIFS(GetFiles!$D$1:$D$5000,$A$1,GetFiles!$F$1:$F$5000,A4)

it is working GREAT!!

Thanks for all you guys help

 

[Oeldere]

See the solutions for your question in the uploaded file (on a free Dutch site).

I made the solution with pivottable, sumproduct, countifs.

Pivottable - sumproduct = sumifs (oeldere).xlsx downloaden

 

[camle]

See the solutions for your question in the uploaded file (on a free Dutch site).

I made the solution with pivottable, sumproduct, countifs.

Pivottable - sumproduct = sumifs (oeldere).xlsx downloaden

Very Nice! Works very good

Thank You!

 

[camle]

See the solutions for your question in the uploaded file (on a free Dutch site).

I made the solution with pivottable, sumproduct, countifs.

Pivottable - sumproduct = sumifs (oeldere).xlsx downloaden

Very good Examples.
By looking at the excel file, I understand...... and now its not as hard as I made it out to be.

 

[Oeldere]

Thanks for the reply.

Glad I could help you.

Even better you learned something new today.