Selective formula
 

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

Selective formula
 

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

Selective formula
 

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


.

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


.

Selective formula
 

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: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.

Selective formula
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"AleVe" wrote in message
...
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: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.