sum values in a col only if value in next col is desired value

  • Thread starter Thread starter Guest
  • Start date Start date
u999rbm said:
add numbers in col A if values in col B = M only
Excel 2003
Click to expand...


Hi U,

If a worksheet formula would suit, try:

=SUMIF(B1:B100,"=M",A1:A100)
 
You can use both worksheet functions or VBA to solve the problem.
First of all, let's see additional worksheet function solutions using array
functions techniques.

For example, if you got a list of numbers in A1:A20, and you got those "M"
in the adjacent column (B).

You can type the array formula:
=SUM(A1:A20*((B1:B20)="M"))

To input an array formula, after typing the formula, hold Ctrl+Shift then
press Enter.

Or, you can use the following "more clever" array formula:
=SUM(A1:A20*(OFFSET(A1:A20,,1)="M"))


Then, let's see the VBA solution.

With the cells in column A selected, run the macro below.

Sub example_macro()
Dim a As Range
Dim cell As Object
Dim answer
Set a = Selection 'can replace this with another range object description

For Each cell In a
If cell.Offset(0, 1).Value = "M" Then
answer = answer + cell.Value
End If
Next

MsgBox answer
End Sub

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
A For Each in Range... with a .Find(what:="TJ", ... in it problem 15
sum visible amounts 2
Column number of last column that returns value ignore formulas 8
Six columns randon no.'s no dupes 6
Maximum value as well as Sum 1
Reconstructing a table with Arrays 1
Only work if target is in particular col 2

Back
Top