Sum all search results?

[Guest]

What's the easiest way to search through Column A for a number (multiple
instances), and summing up all the numbers in Column B on the same row as the
numebrs found in Column A?

 

[Vasant Nanavati]

=SUMIF(A:A,33,B:B)

if you are searching for the number 33, for example.
_______________________________________________________________________

 

[Incidental]

Hi

Not entirely sure what you are asking for but i have decided to go
with for each of the used cells in column A if the cell contains a
number then take the number in column B for that row add it to a
running total that will show at the end???
The code below will do just that though if it's not what you are
looking for it should at least give you somewhere to start.

Option Explicit
Dim MyCell, MyRng As Range
Dim SumTotal As Integer
Dim LstRow As Integer

Private Sub CommandButton1_Click()

SumTotal = 0

LstRow = [A65535].End(xlUp).Row

Set MyRng = Range("A1:A" & LstRow)

For Each MyCell In MyRng

If IsNumeric(MyCell.Value) = True Then

SumTotal = SumTotal + MyCell.Offset(0, 1).Value

End If

Next MyCell

MsgBox SumTotal

End Sub

hope this is of some help

S

 

[Dave Peterson]

I would use
=sumif(a:a,789,b:b)
in a cell in a worksheet

In code, I'd use:

with worksheets("sheet1")
msgbox application.sumif(.range("a:a"),789,.range("b:b"))
end with

 

[Guest]

Vasant's approach can also be implemented in VBA if you must do this as part
of a macro.

With Worksheets("Sheet1")
Tot = Application.Countif(.Columns(1),.Range("A1"),.Columns(2))
End with

where cell A1 contains the number you are looking for, but that could be in
any cell. For example Z22

With Worksheets("Sheet1")
Tot = Application.Countif(.Columns(1),.Range("Z22"),.Columns(2))
End with

or just in a variable

num = 32
With Worksheets("Sheet1")
Tot = Application.Countif(.Columns(1),num,.Columns(2))
End with

 

[V]

hi
I'd just like to confuse things further, I have an accounts table showing
income. I would like to total the results of two columns
eg. if my income is due to "membership" then I would like to total the
"membership" income, which is shown in columns D, E and F (voucher, cheque,
and cash)
I tried the formula below but it only seems to total d3:d21 even tho i've
stated d3:f21

=SUMIF(B3:B21,"membership",D3:F21)

The only way I can get it to work is by using the following formula

=SUM(SUMIF(B3:B21,"membership",D321),SUMIF(B3:B21,"membership",E3:E21),SUMIF(B3:B21,"membership",F3:F21))

which as you can see is very long, is there a shorter, quicker formula?