I used a command a while back and can only vaguely remember how to use it. I
was using the SumProductIF. I have a column of references and I need to count
how many times the reference is used.

For example:

Date Reference Amount #of Lines per Ref
12/31/09 Payroll 12/09 50.00 2
12/31/09 Payroll 12/09 100.00 2
12/31/09 Sales 12/09 2,000.00 3
12/31/09 Sales 12/09 5,000.00 3
12/31/09 Sales 12/09 10,000.00 3

#of Lines per Ref is the total of how many times that reference is used in
the whole spreadsheet.

Try this formula
=COUNTIF(B:B,B2)
Click yes if I helped

--
Greatly appreciated
Eva


"klafert" wrote:

I used a command a while back and can only vaguely remember how to use it. I
was using the SumProductIF. I have a column of references and I need to count
how many times the reference is used.

For example:

Date Reference Amount #of Lines per Ref
12/31/09 Payroll 12/09 50.00 2
12/31/09 Payroll 12/09 100.00 2
12/31/09 Sales 12/09 2,000.00 3
12/31/09 Sales 12/09 5,000.00 3
12/31/09 Sales 12/09 10,000.00 3

#of Lines per Ref is the total of how many times that reference is used in
the whole spreadsheet.

This worked great, but can I expanded on this question. Sometimes they will
have the same reference but different dates.

Date Reference AMT Amt #of Lines per Ref
12/15/09 Payroll 12/09 50.00 2
12/15/09 Payroll 12/09 100.00 2
12/31/09 Payroll 12/09 2,000.00 3
12/31/09 Payroll 12/09 5,000.00 3
12/31/09 Payroll 12/09 10,000.00 3

#of Lines per Ref is the total of how many times that reference is used in
the whole spreadsheet. Note different dates but same reference.


"Eva" wrote:

Try this formula
=COUNTIF(B:B,B2)
Click yes if I helped

--
Greatly appreciated
Eva


"klafert" wrote:

I used a command a while back and can only vaguely remember how to use it. I
was using the SumProductIF. I have a column of references and I need to count
how many times the reference is used.

For example:

Date Reference Amount #of Lines per Ref
12/31/09 Payroll 12/09 50.00 2
12/31/09 Payroll 12/09 100.00 2
12/31/09 Sales 12/09 2,000.00 3
12/31/09 Sales 12/09 5,000.00 3
12/31/09 Sales 12/09 10,000.00 3

#of Lines per Ref is the total of how many times that reference is used in
the whole spreadsheet.

Try the below and copy down as required

=SUMPRODUCT(($A$2:$A$100=A2)*($B$2:$B$100=B2))

--
Jacob


"klafert" wrote:

This worked great, but can I expanded on this question. Sometimes they will
have the same reference but different dates.

Date Reference AMT Amt #of Lines per Ref
12/15/09 Payroll 12/09 50.00 2
12/15/09 Payroll 12/09 100.00 2
12/31/09 Payroll 12/09 2,000.00 3
12/31/09 Payroll 12/09 5,000.00 3
12/31/09 Payroll 12/09 10,000.00 3

#of Lines per Ref is the total of how many times that reference is used in
the whole spreadsheet. Note different dates but same reference.


"Eva" wrote:

Try this formula
=COUNTIF(B:B,B2)
Click yes if I helped

--
Greatly appreciated
Eva


"klafert" wrote:

I used a command a while back and can only vaguely remember how to use it. I
was using the SumProductIF. I have a column of references and I need to count
how many times the reference is used.

For example:

Date Reference Amount #of Lines per Ref
12/31/09 Payroll 12/09 50.00 2
12/31/09 Payroll 12/09 100.00 2
12/31/09 Sales 12/09 2,000.00 3
12/31/09 Sales 12/09 5,000.00 3
12/31/09 Sales 12/09 10,000.00 3

#of Lines per Ref is the total of how many times that reference is used in
the whole spreadsheet.