Formula to only count non-duplicates?

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 ;)
gisvpnAsked:
Who is Participating?
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
gisvpnAuthor Commented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
zorvek (Kevin Jones)ConsultantCommented:
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
 
gisvpnAuthor Commented:
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
 
gisvpnAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.