List comparison and quantification of results

[mattrhorn]

Hi everyone, I am really turned around on what I thought would be an easy task. I have searched other threads and have not found anything that helps, but if there is one, feel free to redirect.

Task:
Given a list of ZIP codes from a client, show how many are in our service area (separate ZIP code list.)
Ideally show this data as a percentage of total.

Bonus:
Can I make this transferrable between workbooks, as I will be recieving many lists per week that need the same analysis.

I have tried basic stuff like modified countif/count functions that never seem to deliver the proper results.
I tried to build a pivot table but I couldn't figure how to make that work either.

I used to be pretty good at figuring this stuff out, but am having no luck on this one.

Thanks for your input.

[dilipandey]

Hi mattrhorn,

Welcome to the forum.

Suggest you to upload a sample workbook.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[mattrhorn]

Suggest you to upload a sample workbook.. thanks.

Here you go. I just pulled the column of data that I need to do this action for.

I put our service area ZIP codes in D1 rather than attaching a separate sheet.

Thanks for your help!

[dilipandey]

Hi Mattrhorn,

I used below formula to check if the code in column A is covered in your service area codes in D1:-

Please Login or Register  to view this content.

Then I used below formula to calculate the %

Please Login or Register  to view this content.

See attached:Trial census ZIP.xls


Regards,
DILIPandey

<click on below 'star' if this helps>

[mattrhorn]

That works! I was overcomplicating the COUNTIF function in my original attempts.

[dilipandey]

cheers

Regards,
DILIPandey

<click on below 'star' if this helps>