?
Solved

Formula to only count non-duplicates?

Posted on 2012-08-12
7
Medium Priority
?
381 Views
Last Modified: 2012-08-12
I have a formula as below :

=SUMIF(Data!E:E,Summary!F15,Data!J:J)

This basically looks for a code in column E and adds up each instance where the code is found with the figure in column J (for the value defined in Summary!F15).

I would like to do something slightly different with the formula.

Instead of using column J to count up a total, I would like to look at column F which has a name in it and count the number of unique names in it and give a total of this?

Can this be done?

GISVPN ;)
0
Comment
Question by:gisvpn
  • 4
  • 3
7 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 38285578
The formula below counts the number of unique numeric and text values in a column:

   =SUMPRODUCT((J1:J100<>"")/COUNTIF(J1:J100,J1:J100&""))

Kevin
0
 

Author Comment

by:gisvpn
ID: 38285596
Hi Kevin,

Thanks for the formula - how do i ensure I only count the unique entries against the value in Summary!F15 which appears in column E - at the same time?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 38285623
Use this array formula. Press CTRL+SHIFT+ENTER to enter it.

=SUM(IF(FREQUENCY(IF((LEN(J1:J100)>0)*(E1:E100=Summary!F15),MATCH(J1:J100,J1:J100,0)),ROW(J1:J100)-ROW(J1)+1),1))

Note that the part "ROW(J1:J100)-ROW(J1)+1" must be adjusted accordingly if the first row of data is not in row 1. For example, if the first row of data is in row 2 then use:

ROW(J2:J100)-ROW(J2)+1

Kevin
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gisvpn
ID: 38285670
Hi Kevin,

I dont seem to get the expect answer - attached is an example spreadsheet - where I have the formula I am expecting 10 as a number - I currently get 1. Any ideas ;)
Book4.xlsx
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 38285678
Use this formula:

=SUM(IF(FREQUENCY(IF((LEN(data!F$1:F$49796)>0)*(data!E$1:E$49796=Summary!F15),MATCH(data!F$1:F$49796,data!F$1:F$49796,0)),ROW(data!F$1:F$49796)-ROW(data!F1)+1),1))

and press CTRL+SHIFT+ENTER when entering the formula (you did not do so).

Also, you have to un-merge the cells containing the formula.

Kevin
Copy-of-Book4.xlsx
0
 

Author Comment

by:gisvpn
ID: 38285744
Great thanks - pity you cant use a merged cell with this - do you know why.

Also do you know what CTRL+SHIFT+ENTER  does? when adding the { }?

Thanks,

GISVPn
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 38286196
CTRL+SHIFT+ENTER instructs Excel to enter the formula as an array formula. Array formulas behave differently than regular formulas.

I can't explain well why you can't put an array formula in a merged range without you having a more in-depth understanding of how array formulas work. Quite frankly, I'm not sure it makes a lot of sense to me anyway. My guess is it has something to do with the fact that a type of array formula - not the one we are discussing here - is entered into multiple cells, and a merged range consists of two or more cells hence Excel can't (or does not want to) distinguish between a single cell array formula and a multiple cell array formula in that case - so it punts and says we can't do it.

Kevin
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question