How to use SumProduct to count reference or something else?

[klafert]

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.

 

[Eva]

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

 

[klafert]

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.

 

[Jacob Skaria]

Try the below and copy down as required

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