# Help, i need a formula!

#### sgremmo

##### Board Regular
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
3 4 Y count 2
4 2 N
5 1 Y count 3
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

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
You can use a pivot table to eliminate the duplicate entries, then use SUMIF to total the B-Yeses.

Try this formula

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

which must be confirmed with CTRL+SHIFT+ENTER

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

Hi Simone,

is this what you want?
Book1
ABCDE
12Y
22Y3
34Y
42N
51Y
64Y
72Y
8
Sheet1

fomula in D2

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

confirmed with CTRL+SHIFT+ENTER

nacturally my reply was to mbt'r!

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

(for function there's time)

Simone

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

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

Replies
44
Views
2K
Replies
9
Views
193
Replies
3
Views
102
Replies
3
Views
155
Replies
3
Views
301

1,219,519
Messages
6,148,748
Members
450,833
Latest member
Andyboi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back