Selective formula

[AleVe]

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

 

[T. Valko]

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.

 

[AleVe]

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]

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.

 

[AleVe]

Hi,

Many thanks your formula is correctly working, I have the result I was
looking for.

Alex

 

[T. Valko]

You're welcome. Thanks for the feedback!