Formula to only count non-duplicates?
I have a formula as below :
=SUMIF(Data!E:E,Summary!F1
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 ;)
=SUMIF(Data!E:E,Summary!F1
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 ;)
ASKER
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?
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?
Use this array formula. Press CTRL+SHIFT+ENTER to enter it.
=SUM(IF(FREQUENCY(IF((LEN(
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
=SUM(IF(FREQUENCY(IF((LEN(
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Also do you know what CTRL+SHIFT+ENTER does? when adding the { }?
Thanks,
GISVPn
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
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
=SUMPRODUCT((J1:J100<>"")/
Kevin