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

add numbers in col A if values in col B = M only
Excel 2003

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

"u999rbm" wrote in message
...
add numbers in col A if values in col B = M only
Excel 2003


Hi U,

If a worksheet formula would suit, try:

=SUMIF(B1:B100,"=M",A1:A100)


---
Regards,
Norman

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

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

http://www.vonixx.com


"u999rbm" wrote:

add numbers in col A if values in col B = M only
Excel 2003

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

What is =SUMIF(B1:B10,"M",A1:A10) ?

Good luck.

Ken
Norfolk, Va