Complex SUMIF
 

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.

Complex SUMIF
 

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.

Complex SUMIF
 

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.

Complex SUMIF
 

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.

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.