Prev: can I disable the delete command for a file
Next: Linking option buttons and returning data across worksheets
From: T. Valko on 3 Mar 2010 12:39
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"AleVe" <alex93200(a)hotmail.com> wrote in message
news:C0FF72A1-6D82-48DF-923E-A53F442CFF11(a)microsoft.com...
> Hi,
>
> Many thanks your formula is correctly working, I have the result I was
> looking for.
>
> Alex
>
> "T. Valko" wrote:
>
>> >The Test Scenarii # are also with format "General"
>> >but they're composed this way: AA.BB_C1_432
>>
>> OK, that's why it didn't work on your REAL data. The formula I suggested
>> will only work on numbers while your REAL data is TEXT (alphanumeric).
>>
>> This is why it's important to post REAL information when asking a
>> question.
>> Don't use made up scenarios/data.
>>
>> Try this version. Assumes no empty cells in the column B range.
>>
>> Array entered** :
>>
>> =SUM(IF(FREQUENCY(IF(A2:A12="A",MATCH(B2:B12,B2:B12,0)),ROW(B2:B12)-ROW(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
>>
>>
>> "AleVe" <alex93200(a)hotmail.com> wrote in message
>> news:1976D401-8009-4ACB-BD65-049139AB6054(a)microsoft.com...
>> > Hi,
>> >
>> > Thanks for the anwser. I tried with the given table (in my previous
>> > post)
>> > and it works correctly, but the strange thing is when I test with my
>> > existing
>> > table, it only calculates "0".
>> >
>> > I wonder if it could be due to a specific format of my cells, I will
>> > give
>> > you more information about what my cells contain.
>> >
>> > The devices are only words without numbers in it (format of cells is
>> > "General")
>> > The Test Scenarii # are also with format "General" but they're composed
>> > this
>> > way: AA.BB_C1_432
>> >
>> > Other point which could may be important (but I don't think as the
>> > formula
>> > perform a frequency count), my devices are not listed in a specific
>> > order,
>> > thy're present randomly at the begining or in the middle of the column
>> > without any specific order.
>> >
>> > Hope this could give you hints to understand my issue :)
>> >
>> > Thanks again.


First  |  Prev  | 
Pages: 1 2
Prev: can I disable the delete command for a file
Next: Linking option buttons and returning data across worksheets