Count transacton

Ramzein

Not sure how many customers you have but the simplest way to count the transactions for a customer is using the worksheet function COUNTIF....

If each invoice counts as a tranaction (but some businesses may have several invoices per order) and you have invoice details in rows 2 to 30 (say) then put this in a cell: 

=COUNTIF(B2:B30,"=CustomerID")

and (once you change the bits in bold above, e.g. replacing CustomerID with a real value) that cell will count them.

Be careful not to introduce spaces before or after CustomerID in the equation (or transaction list) or the result will be wrong.

REVISION 1:  I've removed the wording about using the worksheet function COUNTIF in VBA, since for some odd reason I can't use any worksheet functions in VBA at present, not even SUM!

To illustrate the formulae above, I've attached a version of the file I sent in reply to your question last week. In this, I've added several invoices from the Invoice sheet but changed the CustomerID for a couple of the rows.

In this file please see the yellow cells B1:D1 in the "Invoice Inventory" sheet. Here:

B1 includes a customerID

C1 is the COUNTIF formula with the CustomerID "hard coded" in the formula:

=COUNTIF(B4:B30,"=smc001")

D1 refers to B1 instead (so you can change)

=COUNTIF(B4:B30,B1)

Both C1 and D1 correctly report 3 transactions for "smc001"

REVISION 2: see second attached file Count trans v0_a.xlsx (a version of what you just sent)where the formula in A1 of Invoice Invenrtory counts the transactions for a hard-coded customer.

REVISION 3: There's something odd about the file "Invoice macros.." from last week which disables WorksheetFunctions somehow but they DO work onother workbooks (phew!) so...

I've replaced the second file with a macro-enabled version Count trans v0_b.xlsm  which also has a macro in Sheet10 (Invoice Main (2)) which takes a CustomerID from the use and preport the count in a message box. Here's the simple code (in which you might need to chnage the bit in bold later on or autodetect the use in that column):

Sub CountTrans()

r = InputBox("Enter a CustomerID:", "Count transactions", "smc046")

MsgBox Application.WorksheetFunction.CountIf(Range("C3:C30"), r) & " transactions counted for " & r

End Sub