Prev: How do I get a sum of every seventh cell in a column?
Next: Formula results display only after pressing Enter key
From: T. Valko on 2 Mar 2010 12:15
>the same result using SUMPRODUCT,
>but I am afraid it might not be possible.

I'm sure it's *possible* but the formula I suggested is the *most efficient*
at counting uniques if using only the built-in functions. A SUMPRODUCT
version would not be very efficient on 5000+ rows of data.

If you want the most efficient *method* possible then you'd need to go with
a VBA UDF (user defined function). The most efficient UDF that I know of is
included in a free add-in called Morefunc.xll.

--
Biff
Microsoft Excel MVP


"Paulo" <Paulo(a)discussions.microsoft.com> wrote in message
news:E8B11056-DD88-4B18-A60D-5E64746CDE71(a)microsoft.com...
> Thanks, but I am already aware of this formula. There was a previous post
> where this solution was proposed. What I really wanted was a way of
> getting
> the same result using SUMPRODUCT, but I am afraid it might not be
> possible.
>
> Regards,
>
> Paulo
>
> "T. Valko" wrote:
>
>> Try this array formula** :
>>
>> =SUM(IF(FREQUENCY(IF(All!M2:M5514=A22,MATCH(All!B2:B5514,All!B2:B5514,0)),ROW(All!B2:B5514)-ROW(All!B2)+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.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Paulo" <Paulo(a)discussions.microsoft.com> wrote in message
>> news:461059FE-C070-4E91-9F26-4D4ABF3935D9(a)microsoft.com...
>> > No, but then the first part of the function (all!$B$2:$B$5514<>"")
>> > would
>> > have
>> > taken care of that.
>> >
>> > Let's assume there are no empty cells, if this helps.
>> >
>> > Thanks,
>> >
>> > Paulo
>> >
>> >
>> > "T. Valko" wrote:
>> >
>> >> Are there any empty cells within the range all!B2:B5514?
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Paulo" <Paulo(a)discussions.microsoft.com> wrote in message
>> >> news:9B196D00-3457-4F53-BA95-B96F8E6B653C(a)microsoft.com...
>> >> > This question was posted before but the solution proposed used
>> >> > different
>> >> > functions. I would like to insist. Is there a modification to the
>> >> > formula
>> >> > below, using SUMPRODUCT, that allows to retrieve the number of
>> >> > unique
>> >> > itens
>> >> > that meet a 2nd criteria?
>> >> >
>> >> > Original formula:
>> >> > =SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))
>> >> >
>> >> > In my case, I need the numbers of unique branches (column B) that
>> >> > meet
>> >> > a
>> >> > specifc criteria in another column (M). So, I have the formula:
>> >> >
>> >> > =SUMPRODUCT((all!$B$2:$B$5514<>"")/COUNTIF(all!$B$2:$B$5514,all!$B$2:$B$5514&""))
>> >> >
>> >> > but I need to include the 2nd criteria:
>> >> >
>> >> > (all!$M$2:$M$5514=$A22)
>> >> >
>> >> >
>> >> > Thanks in advance,
>> >> >
>> >> > Paulo
>> >> >
>> >> >
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>


First  |  Prev  | 
Pages: 1 2
Prev: How do I get a sum of every seventh cell in a column?
Next: Formula results display only after pressing Enter key