Hello I have several columns of data that contain 3 different types of numbers, with every third number in a given column for which I want to count the number of unique values. I've tried editing existing array formulas to try to pull it off, but no luck. Is there a formula, either direct or array, that I could use to do this?
Upload an example workbook showing your layout and where you would want your formula and what results you would expect. See yellow banner at top of page.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Please see attached fle
If you don't have blank cells in L5:L36, try
=SUMPRODUCT(1/COUNTIF(L5:L36,L5:L36))
If you do have blank cells you don't want included in the distinct count, try
=SUMPRODUCT((1-ISBLANK(L5:L36))/(COUNTIF(L5:L36,L5:L36)+ISBLANK(L5:L36)))
ADDED: sorry I mised the every 3rd number part.
=COUNT(1/FREQUENCY(IF((MOD(ROW(L5:L36)-ROW(L5),3)=2)*NOT(ISBLANK(L5:L36)),MATCH(L5:L36,L5:L36,0)),ROW(L5:L36)-ROW(L5)))
Last edited by hrlngrv; 11-16-2020 at 09:27 PM. Reason: addendum
If you just need the number of unique numbers, I used this adaptation of hringrv's formula (thanks hringrv) in G13
Entered as an ARRAY w/ CNTRL SHFT ENTERFormula:
=SUM(COUNT(1/FREQUENCY(IF((MOD(ROW(A1:A30),3)=1)*NOT(ISBLANK(A1:A30)),MATCH(A1:A30,A1:A30,0)),ROW(A1:A30))))
For a listing of the numbers (you want a unique list of numbers, not a listing of unique numbers), I used a helper column. In B1 copied down
In F13 copied downFormula:=IF(MOD(ROW(A1),3)=1, IF(COUNTIF($A$1:$A1,A1)<2,A1,""),"")
Formula:=IFERROR(INDEX($A$1:$A$30,AGGREGATE(15, 6,ROW($A$1:$A$30)/($B$1:$B$30<>""),ROWS($F$13:$F13))),"")
I am not currently on O365, otherwise, I believe you could have used the UNIQUE function to simplify this.
Hello, thanks. I tried the first formula, and it returned a result of 1. I was trying to get an answer of 9. Of the 10 numbers in that sequence, 9 are unique. Can this formula be tweaked to get the current answer?
Quibble: ROW(A1:A30) rather than ROW(L5:L36)-ROW(L5) is fine for data beginning in row 1. Less fine when data begins further down. However, the only absolutely robust way to generate arrays of sequential integers is ROW(INDEX($1:$1048576,1,1):INDEX($1:$1048576,ROWS(RANGE),1)), which is immune to row insertion/deletion. Unless one has a version of Excel with SEQUENCE.
As for listing distinct numbers from RANGE, why not use an advanced filter?
You probably didn't enter the formula as an array. **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
That did the trick - thanks to all who responded!
Maybe a pivottable can help with this.
See attachment........
Kind regards,
Piet Bom
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
TXMcNasty 
Register To Reply
Bookmarks