Complex SUMIF
The macro I posted will give the results you are looking for. A simple
worksheet formula wil not seperate comma seperated data.
"shaqil" wrote:
On Nov 6, 5:10 pm, Joel wrote:
try this code
call with
=suminvoices(A7,B1:B3)
A7 is the string with invoice numbers, B1:B3 is the range of invoices. code
assumes Amount is one cell to the right of the Invoice Number
Function suminvoices(InvoiceString As String, _
ByRef Invoices As Range)
suminvoices = 0
Trim (InvoiceString)
Do While Len(InvoiceString) 0
If InStr(InvoiceString, ",") 0 Then
Invoice = Val(Trim(Left(InvoiceString, _
InStr(InvoiceString, ",") - 1)))
InvoiceString = Val(Trim(Mid(InvoiceString, _
InStr(InvoiceString, ",") + 1)))
Else
Invoice = Val(InvoiceString)
InvoiceString = ""
End If
For Each cell In Invoices
If cell = Invoice Then
suminvoices = suminvoices + _
cell.Offset(rowoffset:=0, columnoffset:=1)
End If
Next cell
Loop
End Function
"Tom Ogilvy" wrote:
How is this request different from your previous request back on Oct 10/11?
and which was answered.
--
Regards,
Tom Ogilvy
"shaqil" wrote:
Dear All,
I have data in following form and I want to sum of invoices which have
been written with "," (comma), e.g. 151,153 sum of Rs. 35.
Can any one help me to write this formula.
chq # Inv.# Amount
152 10
151 15
153 20
A 151,153 ?
Thnx in advance.- Hide quoted text -
- Show quoted text -
It is not giving me desired result of 35. it sums only one invoice and
I need total of both invoices.
|