Help, i need a formula!

[sgremmo]

I need to verify for each row that B values are Y and count value in column A (count only one time each value)
<pre>
row A B
1 2 Y count 1
2 2 Y already counted
3 4 Y count 2
4 2 N
5 1 Y count 3
6 4 Y already counted
7 2 Y already counted
8
9 Total Y = 3
</pre>
I'm only near with sum.if but i'm not able to count only one time row 1, rov 2 and row 7 so as row 3 and row 6.

Tanks and regard

Simone

 

[mtb'r]

You can use a pivot table to eliminate the duplicate entries, then use SUMIF to total the B-Yeses.

 

[barry houdini]

Try this formula

=COUNT(1/FREQUENCY(IF(B1:B10="Y",A1:A10),A1:A10))

which must be confirmed with CTRL+SHIFT+ENTER

 

[sgremmo]

no, i have more 30 blocks of 20 row and for each block need to have a number as if it is a result of i.e. SUM(A1:A20).

Now I'm look for a function... but ? don't know if it is a right way!

Thank for yor help.

Simone

 

[barry houdini]

Hi Simone,

is this what you want?

Book1
	A	B	C	D	E
1	2	Y
2	2	Y		3
3	4	Y
4	2	N
5	1	Y
6	4	Y
7	2	Y
8
Sheet1

fomula in D2

=COUNT(1/FREQUENCY(IF(B1:B10="Y",A1:A10),A1:A10))

confirmed with CTRL+SHIFT+ENTER

 

[sgremmo]

nacturally my reply was to mbt'r!

barry houdini's formula work exactly I need, thank very much!

(for function there's time)

Simone

 

[sgremmo]

I must write this because other people could need this "excellent" formula writted by barry houdini:

COUNT(1/FREQUENCY(IF(B1:B10="Y",A1:A10),A1:A10))

work fine with one mistake: when data cell are empty return 1 instead of 0.

I replace it with
if(count(B1:B10="Y",A1:A10)>0,
count(1/frequency(if (B1:B10="Y",A1:A10),A1:A10)),)


To help who need translate formula from english in to other 8 languages, i foud http://www.excelling.it/excel/modelli/Excel_multilingua.asp

 

[sgremmo]

I've a new problem: formula is correct if is necessary count only Y.

But if I write in a second cell to count how many N and in a third how many P formulas starts work wrongly!

Thank

Simone