Complex SUMIF

[shaqil]

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.

 

[Guest]

How is this request different from your previous request back on Oct 10/11?
and which was answered.

 

[Guest]

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

 

[shaqil]

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






- Show quoted text -

It is not giving me desired result of 35. it sums only one invoice and
I need total of both invoices.

 

[Guest]

The macro I posted will give the results you are looking for. A simple
worksheet formula wil not seperate comma seperated data.