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.

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.
>
>

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.
> >
> >

On Nov 6, 5:10 pm, Joel <J...@discussions.microsoft.com> 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.

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 <J...@discussions.microsoft.com> 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.
>
>