How to use SumProduct to count reference or something else?
 

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.

How to use SumProduct to count reference or something else?
 

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.

How to use SumProduct to count reference or something else?
 

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.

How to use SumProduct to count reference or something else?
 

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.