Thread: Complex SUMIF
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
joel joel is offline
external usenet poster
 
Posts: 9,101
Default 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.