brian sullivan
Member
Greetings,
I'm reaching out to the Chandoo community for a question where I'm close to the answer, but need some support on finalizing multiple formulas. (My questions are attached in the excel spreadsheet includes my questions with written out answers in text). I partially understand the base formula =Sumproduct(1/Countif(data, data, & "")) which accounts for counting ranges & not including blank data.
Data Questions Summary
My first question is pinpointing unique count among companies by year & location. For example, in 2015 the US unique count would be 2. This represents the number of unique instances a company exists (even though the actual count is 5). The answer in 2016 would be 1 (even though the actual count is 3).
Then my second question is what percentage of these companies with an unique count are Domestic vs Foreign by location. For example, in 2015 US would be 50% (Domestic) & 50% (Foreign) because only two companies have an unique count (split between Domestic/Foreign). The third company company in the US (Subway) has no count; therefore omitted from this equation.
Thanks,
Brian
I'm reaching out to the Chandoo community for a question where I'm close to the answer, but need some support on finalizing multiple formulas. (My questions are attached in the excel spreadsheet includes my questions with written out answers in text). I partially understand the base formula =Sumproduct(1/Countif(data, data, & "")) which accounts for counting ranges & not including blank data.
Data Questions Summary
My first question is pinpointing unique count among companies by year & location. For example, in 2015 the US unique count would be 2. This represents the number of unique instances a company exists (even though the actual count is 5). The answer in 2016 would be 1 (even though the actual count is 3).
Then my second question is what percentage of these companies with an unique count are Domestic vs Foreign by location. For example, in 2015 US would be 50% (Domestic) & 50% (Foreign) because only two companies have an unique count (split between Domestic/Foreign). The third company company in the US (Subway) has no count; therefore omitted from this equation.
Thanks,
Brian
