Trying to calculate tier 1, 2 and 3 countries

[zep101]

Hi I have a spreadsheet (attached)

It is an email subscriber list, tab 1 is the data, tab 2 are the defined country tiers and tab 3 is for calculation list and pie chart (I guess tab 3 could be included in tab 2, there is room. Whatever is the easiest)

I want to define how many subscribers are in tier 1, tier 2 and tier 3 (all others)

I would like to keep tabs 2 and 3 but be able to past new data into tab one to see the results every month or so, etc

I work with excel a lot but not much with these kinds of calculations. If anyone can help me it will be much appreciated.

Let me know if you need more info. Thanks, Joe

Attachment 359405

[Sam Capricci]

your request is rather vague. It almost looks like you are wanting some kind of countif formula but I cannot tell from your narrative and your attachment. Could you better define your request? Perhaps something about the results you are hoping for?

[daffodil11]

Maybe with SUMPRODUCT or SUMIF?

Instead of separate columns of Countries, create a more data friendly list. Country, Tier. Summarization is much easier.

I tend to use SUMPRODUCT for everything.

[zep101]

Sorry I will try to explain better.

I need to sum all the occurrences of tier 1 countries, tier 2 countries and tier 3 countries being all the rest that are not 1 and 2

So tab 1 column J I need to count/sum all the occurrence of the tier 1 countries (United States, Canada, United Kingdom, Australia and New Zealand)

Does this help?

[shg]

Maybe this?

Row\Col	A	B	C	D	E
1	1400	279	582		A1: =SUMPRODUCT(COUNTIF('11-12-14'!$J:$J, 'Countyy Tiers'!A3:A47))
2	Tier 1	Tier 2	Tier 3		B1: =SUMPRODUCT(COUNTIF('11-12-14'!$J:$J, 'Countyy Tiers'!B3:B47))
3	United States	Argentina			C1: =COUNTA('11-12-14'!$J:$J) - SUM(A1:B1) - 1
4	Canada	Austria
5	United Kingdom	Aruba
6	Australia	Barbados
7	New Zealand	Belgium
8		Bermuda
9		Brazil

[zep101]

Hey that's it shg! I plugged it into my spreadsheet and its working fine. I just gotta figure out now how to make a pie chart out of it

[Sam Capricci]

that was not clear but a reason i mentioned a countif formula. The countif will count (and provide you with a total) like a sum would. So for example you want to count the number of instances (to get a total) of "United States" you could use =COUNTIF('11-12-14'!J:J,A2)

[FDibbins]

I have removed the file attachments relating to the OP's thread, because they look like they contain senistive info (IP addy's)

[shg]

You're welcome.