Selective formula
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:B1 2,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" wrote in message
...
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.
"T. Valko" wrote:
Try this array formula** :
=SUM(IF(FREQUENCY(IF(A2:A12="A",B2:B12),B2:B12),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" wrote in message
...
Hi all,
I would like to create a chart showing spefici numbers regarding a
specific
count made on a column. To illustrate my need, please see hereunder:
Considering I have a table composed of 3 columns:
Column A : Device names
Column B : Test Scenarii numbers
Column C : Test Scripts numbers
Devices Test Scenarii # Test Scripts #
A 1 1
A 1 2
B 1 3
A 1 4
A 2 2
B 2 3
C 2 5
C 3 6
C 3 7
A 4 1
A 4 4
If I need a count of TS numbers per device, I get this:
A - Test Scenarii number = 6 as per the number of test scripts.
The fact is that I would like to get the number of unique Test scenarii
which should give me : A - Test scenarii = 3 (test scsenarii numbers
1, 2
and 4 are the only 3 test scenarii implicated with the device A).
So my question is : Is there any Excel function that could allow me to
get
these results? Or do I need to build a custom function on my own?
Thanks in advance for your answers.
Alex
.
|