How to use SumProduct to count reference or something else?

  • Thread starter Thread starter klafert
  • Start date Start date
K

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.
 
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.
 
Try the below and copy down as required

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