Help! Count not duplicates

I can get you there with 1 helper column

Helper Column: Starting in C2
=IF(COUNTIF(A$2:A2,A2)>1,0,1) This will give us a list of 1's and 0's to help with the count. You will need to author this and copy it down the list.


Count Formula: Put this in H2 -- This is an array formula, you must press cntrl shift and Enter altogether to properly enter it. Excel will add curly braces when you do it right. {} Author this in Cell H2 then drag it down as needed.



=SUM(IF(LEFT($A:$A,LEN($F2))=$F2,$C:$C,0))

See attached:

Not that I can see, but probably... that's a challenging question

Personally,I would just use the helper column.

Originally Posted by GeneralDisarray

Not that I can see, but probably... that's a challenging question

Personally,I would just use the helper column.

I believe so. Try this array-entered formula in H2 and fill down.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

File is attached.

A slightly shorter and less resource heavy version of the above. (Removed a nested IFERROR).

Formula:

Please Login or Register  to view this content.

Also array-entered.

LOL... See... told you it could be done.


Had to screw with that for 15 mins to figure it out Very nice.

Originally Posted by GeneralDisarray

LOL... See... told you it could be done.


Had to screw with that for 15 mins to figure it out Very nice.

Thank you. It took me longer to 'dumb' it out. LOL

Originally Posted by shredraldz

.......Made my day go like crazy.. Thank you so much Guys for the help!
...... :P

You are welcome. Thanks for the feedback and rep.

Originally Posted by CAABYYC

here is another option by using sumproduct

=SUMPRODUCT((MATCH(A1:A11,A1:A11,)=ROW(A1:A11))*(LEFT(A1:A11,LEN(C1))=C1)*1)

Very nice.

If I may suggest one small modification.

Formula:

Please Login or Register  to view this content.

The appended "" in the MATCH keep your formula from breaking in the event there are blank cells in the search range.

Thanks for sharing.

Here's another one.

This array formula** entered in H2 and copied down:

=SUM(IF(FREQUENCY(IF(LEFT(A$2:A$12,LEN(F2))=F2,MATCH(A$2:A$12,A$2:A$12,0)),ROW(A$2:A$12)-ROW(A$2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

The SUM(IF(FREQUENCY array formula is more efficient (faster to calculate) especially on large data sets.

Originally Posted by Tony Valko

Here's another one.

=SUM(IF(FREQUENCY(IF(LEFT(A$2:A$12,LEN(F2))=F2,MATCH(A$2:A$12,A$2:A$12,0)),ROW(A$2:A$12)-ROW(A$2)+1),1))

The SUM(IF(FREQUENCY array formula is more efficient (faster to calculate) especially on large data sets.

It's been awhile since I visited this thread.

Thank you for sharing that one. Until now I had always thought a reciprocating COUNT(1/FREQUENCY array formula was as good as it got.

______