Solved

# Formula to only count non-duplicates?

Posted on 2012-08-12
366 Views
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
Question by:gisvpn

LVL 81

Expert Comment

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

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

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

Author Comment

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

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

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

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.