Hi- So I need to count the number of markets (ex. Flint MI Pittsburgh PA) that are in one column based on whether or not they are assigned to a specific region such as "West", South East etc;
Example: There are multiple markets listed in each region and I need to count how many markets are in each region without counting duplicate market names. Any help would be greatl
Column B: Column C:
Market Name Region
Probably a pivot table, can do that job for you.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
The problem is that there are duplicates in the market column- Reno may be in there 3 to 4 times. Is there a way to use sumproduct or countif to count how many individual market names are in each region? So if Reno and 9 other markets are in the "West" region I need a formula to say if column C states "West" then count all of the markets in B4:B100 and make sure it doesn't count markets in that array twice
Post an excel file, without confidential information.
Please also add the desired result in your sheet.
I've attached a spreadsheet showing what I need. I need the formula to count how many individual markets are in each region without double counting them- So if column B states West for example count only the markets in Column A that are in the West region without counting markets twice.
unique.xlsx
Please see attched
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
That works great, thank you very much
I get different results than those you show in the sample file.
First, change the column headers in F7:I7 to be the actual region.
Central, North East, South Central, West
Then, enter this array formula** in F8 and copy across to I8:
=SUM(IF(FREQUENCY(IF($C8:$C67=F7,MATCH($B8:$B67,$B8:$B67,0)),ROW(B8:B67)-ROW(B8)+1),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
The results I get are:
Central = 6
North East = 9
South Central = 10
West = 6
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Or with an pivot table.
I get also differant result.
See the attached file.
You are welcome.Originally Posted by djreddy
Ok so here is one that may be a litle harder. I need to find formulas to fill in the yellow highlited cells in the attached spreadsheet. I need to count how many stations are in each region depending on which portfolio they are in as well as markets by region based on which portfolio they are in and sum up the cash flow per region based on which portfolio it's in. Any help is greatly appreciated. If someone could just fill in maybe the top region "Central" I can do the rest.
Thanks!
With an pivot table.
See the attached file.
What if I needed the count of the market based on which region but also which portfolio name it falls under..... Please see attached and thank you in advance for any help!
With an pivot table.
See the attached file.
Please reply.
Thank you!
Copy of unique-1.xlsx
Pivot table do not return unique item(unless you use Excel 2013)
Plese see attached.
@RobertMika
In that case this pivot table (with an helpcolumn will do).
See the attached file.
Nice.
You are right -with some help it can be done.
@RobertMika,
Thanks for the reply and clear information.
Thank you both for your help! I really appreciate it!
You are welcome.Thank you both for your help! I really appreciate it!
Ok @ Robert- How would I change that formula to count numbers (say cash flows) based on what portfolio and region they were in? I've attached a file as an example and the yellow highlited cells are where I need the clarification. Thank you!
Thank You
K12:
=SUM(IF(FREQUENCY(IF($E$8:$E$1006<>"",IF($D$8:$D$1006=K$9,IF($C$8:$C$67=$G12,MATCH("~"&$E$8:$E$1006,$E$8:$E$1006&"",0)))),ROW($E$8:$E$1006)-ROW($B$8)+1),1))
Drad down and accross
With an pivot table and a graph.
See the attached file.
Please reply.
@ Robert- I tried the formula to sum up the cash flows in column E but I get 0 which isn't correct. I'm not sure if I'm doing something wrong or not, so when you get a chance could you take a look at the attachment and see where I put the formula in and it returned 0 instead of summing up the total cash flows for the STA portfolio in the central region.
Thank you
Add "STA" in K8
@ Robert- I did what you said and put STA in K9 but I am still getting 0 as the sum of cash flows for STA's portfolio in the central region? Not to sure why so if you could look at the attached file and see what I'm doing wrong I'd appreciate it!
Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks