Help, i need a formula!

sgremmo

Board Regular
Joined
Sep 1, 2004
Messages
55
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

mtb'r

New Member
Joined
Sep 8, 2006
Messages
42
You can use a pivot table to eliminate the duplicate entries, then use SUMIF to total the B-Yeses.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Try this formula

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

which must be confirmed with CTRL+SHIFT+ENTER
 

sgremmo

Board Regular
Joined
Sep 1, 2004
Messages
55
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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
 

sgremmo

Board Regular
Joined
Sep 1, 2004
Messages
55
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

Board Regular
Joined
Sep 1, 2004
Messages
55
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

Board Regular
Joined
Sep 1, 2004
Messages
55
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
 

Forum statistics

Threads
1,141,097
Messages
5,704,313
Members
421,338
Latest member
Pepess

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top