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.

AL.


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

Hi

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
Else
End If

Next x

Range("B323").FormulaR1C1 = Counter

End Sub

Modify as needed.

HTH

Jacob

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

=sumif(A8:B327,A8,B8,B327)
where A8 = lumber


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

=sum(if(A8:A327=A323,B8:B327,0))
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

George

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.

Regards.

Aladin

========