• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I need to Count the number of unique values in a list given a variable criteria

C

Corzid

New Member
1st, thanks for having this forum. I've been working in Excel for years, but not at this level, so having access to your help is greatly appreciated!


2nd, let me see if I can explain this clearly. I have a workbook with 5 tabs, one of which is a list of my Master survey data that has 3 data keys by which I sort the data on 3 other tabs. Specifically, "Zip Code", "Community Name", and "ET#". For each data key, there are multiple values repeated several times each (e.g., several different data rows may use the same zip code or community name).


I've already been able to use CountIf and SumIf statements to sort the survey data based on each occurrence of ET#, zip code, or community name. My problem is this:


I need to count the number of UNIQUE "ET#s" that occur in a particular zip code or a particular community, and I'm not proficient enough in Excel to know what combination of functions are needed or how to put them together. I've included sample data below. I don't know if this makes a difference, but my primary data are located on a separate tab from where I'm summarizing them. The tab names are as follows:


"RNH Data" -- main data page

"ET Smmry" -- summary of data sorted and aggregated by ET#

"Community Smmry" -- summary of data sorted and aggregated by Community Name

"Zip Code Smmry" -- summary of data sorted and aggregated by Zip Code


(And yes, I mentioned to my clients that doing this work in an Access database might be simpler...)


Here is my sample source data. What I need is to do on a second worksheet is summarize how many UNIQUE ETs there are in, say, Lagunitas (7) or Bolinas (5).


Hopefully I've explained this clearly enough. Thanks in advance for everyone's help with this....


David


Source Data

ET# Community Zip Code

001 Lagunitas 94938

001 Bolinas 94924

001 Bolinas 94924

001 Lagunitas 94938

002 Fairfax 94930

002 Lagunitas 94938

002 Fairfax 94930

002 Forest Knolls 94933

003 Bolinas 94924

003 Forest Knolls 94933

003 Fairfax 94930

004 Lagunitas 94938

004 Larkspur 94939

004 Larkspur 94939

004 Inverness 94937

004 Larkspur 94939

005 Corte Madera 94925

005 Corte Madera 94925

005 Lagunitas 94938

005 Corte Madera 94925

005 Forest Knolls 94933

005 Larkspur 94939

005 Corte Madera 94925

005 Marshall 94940

006 Fairfax 94930

006 Inverness 94937

006 Corte Madera 94925

006 Bolinas 94924

006 Larkspur 94939

006 Inverness 94937

007 Marshall 94940

007 Larkspur 94939

007 Lagunitas 94938

007 Corte Madera 94925

008 Lagunitas 94938

008 Bolinas 94924

008 Bolinas 94924

009 Lagunitas 94938

009 Fairfax 94930

009 Lagunitas 94938

009 Fairfax 94930

009 Forest Knolls 94933

010 Bolinas 94924

010 Forest Knolls 94933

010 Fairfax 94930

010 Lagunitas 94938

010 Larkspur 94939

010 Larkspur 94939
 
Hi Corzid!


This sounds similar to this thread:

http://chandoo.org/forums/topic/combine-countifs-and-unique-function


Does Vijay's solution work for you as well?
 
Luke,

Thanks for the quick reply!


I saw Vijay's solution but couldn't get the range and cell references correct when I tried to use the formula in my worksheet (my source data is on a different tab from my results table, which likely added to my confusion). I've provided the column headings and the sample data below. Would you or someone help me construct the formula using the column names or letters shown below?


Thanks,

David


Source Data Results Table

Col A Col B Col F Col G

ET# Zip Code Zip Code # Unique ET Formula????

001 94924 94924 5

001 94924 94925 3

003 94924 94930 5

006 94924 94933 5

008 94924 94937 2

008 94924 94938 8

010 94924 94939 5

005 94925 94940 2

005 94925

005 94925

005 94925

006 94925

007 94925

002 94930

002 94930

003 94930

006 94930

009 94930

009 94930

010 94930

002 94933

003 94933

005 94933

009 94933

010 94933

004 94937

006 94937

006 94937

001 94938

001 94938

002 94938

004 94938

005 94938

007 94938

008 94938

009 94938

009 94938

010 94938

004 94939

004 94939

004 94939

005 94939

006 94939

007 94939

010 94939

010 94939

005 94940

007 94940
 
Hi ,


There was a similar question some time back , for which the following worksheet was uploaded ; you may find it helpful.


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21119


Please copy the entire address and paste it in your browser.


Narayan
 
Narayan,


Thanks for the reference to the downloadable spreadsheet!! I was able to figure out the examples contained on it, determine which one was most appropriate for my needs, and modify that formula for my spreadsheet. I may not yet understand every aspect of the formula I created, but it's giving the correct answers and for right now that's what matters to me!!


Thanks again, Narayan and Luke, for your help!! I really appreciate it!


Sincerely,


David
 
You must log in or register to reply here.
Back
Top