How to combine count and VLOOKUP??

MarkRyan · #1 07-02-2009, 10:43 AM

Hi there,

This is my first time ever posting a query so i hope my discriptin isn't to vauge and any help would be GREATLY appreciated.

I have a file with 3 tabs, "Contract" - "AllCollections" - "Analysis"

In "Contract" there is a fixed table with 26 columns and 3339 rows starting in cell A3 - Z3 (row 3 being titles). In column B there is a list of unique numbers (thus there are 3338 numbers in the list.

In "AllCollections" there is a table with 12 columns starting in cell A4 - L4 (first row being titles) and the rows are updated daily with more data. While data is updated daily each row will contain a number (one of the 3338 unique numbers) in column I.

In the "Analysis" tab i want to be able to count how many times the unique number appears in "AllCollections" (bearing in mind the list will grow each day so it can be there numerous times) and i want to be able to do that for all the numbers in "Contract".

Please let me know if you need a better explanation!

Much appreciated

Mark

Laura2018 · #2 07-02-2009, 04:37 PM

If I am reading this correctly, you may be able to accomplish this with a pivot table and set it to count the unique number occurances.

Good Luck!

kellyfspringer · #3 07-02-2009, 09:54 PM

I'm thinking that you want to see the count for ALL of the numbers in "Contract", regardless of whether they show up on "AllCollections"
If that is the case, you can Copy Column B from "Contract" into Column A in "Analysis" then put this formula in Column B in "Analysis":
=COUNTIF(AllCollections!I:I,Analysis!A4)
You can fill this formula down to the end of your list.

The countif statement will count how many times it sees the number from Column A of Analysis in Column I of AllCollections.
No need for a vlookup.

Welcome to the Excel Forum

Forum Statistics