MrExcel Publishing
Your One Stop for Excel Tips & Solutions

IF statement Problems

Posted by AL on January 10, 2002 6:15 PM

I have many categories in A8:A327, some of them repeat. Lets say I have lumber 4 times somewhere in column A, I need a formula that when it finds lumber anywhere in column A it will take the price next to it in column B and put it in B323, now it needs to accumulate all of the prices that it finds next to lumber in column B to be put in B323.

Thank you for any help.


Posted by Jacob on January 10, 2002 6:29 PM


Try this

Sub AddItUp()
Dim Counter as Single
Dim x as Integer

For x = 8 to 327

If Range("A" & x).Value = "lumber" Then
Counter = Counter + Range("B" & x).Value
End If

Next x

Range("B323").FormulaR1C1 = Counter

End Sub

Modify as needed.



Posted by bruce on January 10, 2002 6:37 PM

where A8 = lumber

Posted by George J on January 11, 2002 2:00 AM

Than press Ctrl Shift and enter. The formula will now have {} around it. Cell B323 will have the totals of the item entered in A323 - Lumber


Posted by Aladin Akyurek on January 11, 2002 3:29 AM

George --

There is no need for a costly array formula here: SUMIF is sufficient and effective.