Excel Double Counting

[nigerla]

Hi All,

I am working on a spreadsheet that has several indentical values. E.g

A1= John Thomas B1= 12/20/2009
A2= John Thomas B2= 12/20/2009
A3= John Thomas B3= 12/20/2009

How do I get use a count formula to count it once instead of twice?

Thanks, I appreciate your help an assistance.

 

[Aladin Akyurek]

Hi All,

I am working on a spreadsheet that has several indentical values. E.g

A1= John Thomas B1= 12/20/2009
A2= John Thomas B2= 12/20/2009
A3= John Thomas B3= 12/20/2009

How do I get use a count formula to count it once instead of twice?

Thanks, I appreciate your help an assistance.

What do you want to count exactly - just the distinct names in column A perhaps?

 

[T. Valko]

Hi All,

I am working on a spreadsheet that has several indentical values. E.g

A1= John Thomas B1= 12/20/2009
A2= John Thomas B2= 12/20/2009
A3= John Thomas B3= 12/20/2009

How do I get use a count formula to count it once instead of twice?

Thanks, I appreciate your help an assistance.

Try this array formula**.

=SUM(IF(FREQUENCY(MATCH(A2:A10&"^"&B2:B10,A2:A10&"^"&B2:B10,0),ROW(A2:A10)-ROW(A2)+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.

Assumes no empty cells within the range.

 

[jvanbonn]

Set a cell to =SUBTOTAL(103,A1:A9999)

Then

Under the DATA Tab (Excel 2007) Choose and Advanced Filter.

With:
Filter the list, in-place
Your Range of Data
Check Unique Records Only

You should end up with a filtered list of unique values that will be counted only if they are visible.

 

[nigerla]

Thanks Valco, I tried your formula but it seems to be giving 1 for all the answers. I just wanted it to count one of the values using the name and date as reference and ignore cells A2:A3.

Do you have any other suggestions?

Thanks for your help.

 

[T. Valko]

Thanks Valco, I tried your formula but it seems to be giving 1 for all the answers. I just wanted it to count one of the values using the name and date as reference and ignore cells A2:A3.

Do you have any other suggestions?

Thanks for your help.

Let's assume this is your data:

Book1
	A	B
1	Name	Date
2	John Thomas	12/20/2009
3	John Thomas	12/20/2009
4	John Thomas	12/20/2009
5	Biff	1/1/2011
6	Biff	1/1/2011
7	Biff	2/25/2011
8	Lisa	1/1/2011
9	Rhonda	12/20/2009
10	Lisa	1/1/2011
Sheet1

This array formula** returns the correct count of 5:

=SUM(IF(FREQUENCY(MATCH(A2:A10&"^"&B2:B10,A2:A10&"^"&B2:B10,0),ROW(A2:A10)-ROW(A2)+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.