IF/WHEN for when for cells greater than a

[dhman08]

I'm trying to use a multiple "multiple" IF/WHEN formula for when cells are greater than 0 and based on that multiply the cells that are greater than 0. For example, looking at the table below in row 2 Sam and GOOGLE are blank meaning their QTY values are 0. So what I'm hoping the formula will do: it'll look at the QTY column of each row and wherever the QTY is grater than 0 it'll take those cells and multiply those cells.

Currently, I have this formula: =IF(B2>0, B2, IF(D2>0,B2*D2), but it isn't doing what I was hoping for it to do. Not sure what I'm doing wrong. I also think my approach isn't the best. Since the table is relatively small, maybe my approach will work for now, but few months from now when I'll have to deal with thousands of cells I'm not sure if this approach will work.

Any help will be much appreciated.

Vendor	Qty	Vendor	Qty	Vendor	Qty	Vendor	Qty
ABC		DEF		GHI	1	JKL
APL	2	SAM		MOTO	3	GOOGLE
BlackBerry		BlueBerry	2	OrangeBerry	2	GreenBerry

<tbody>
</tbody>

 

[KenDue]

One last comment from me and I'll butt out... perhaps if you could get your mentor to describe the objective they are trying to accomplish, the people who are trying to assist you would be in a better position to offer simpler or more productive solutions. As the old saying goes: "there's more than one way to skin a cat".

 

[dhman08]

Okay, I've figured it out. Basically, I just used hard-coded if statements to check for the number of values in each row and perform a calculation based on that. This means, if ever you add additional COLUMNS, you'll need to account for that with additional if statements.

It's not pretty, but it works. Perhaps someone else can improve it.

Sub quantityCalcs()

    Dim ws As Worksheet
    Dim lastRow As Integer, i As Integer, x As Integer, rowResult As Integer
    Dim rRange As Range, rCell As Range
    Dim qtyColl As Collection
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    With ws
    
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        For i = 2 To lastRow
        
            Set qtyColl = New Collection
        
            Set rRange = .Range("A" & i & ":H" & i)
            
            For Each rCell In rRange.Cells
            
                If IsNumeric(rCell.Value) And rCell.Value > 0 Then
                
                    qtyColl.Add rCell.Value
                
                End If
            
            Next rCell
            
            If qtyColl.Count = 1 Then
                
                rowResult = qtyColl.Item(1)
            
            ElseIf qtyColl.Count = 2 Then
            
                rowResult = qtyColl.Item(1) * qtyColl.Item(2)
                
            ElseIf qtyColl.Count = 3 Then
            
                rowResult = qtyColl.Item(1) * qtyColl.Item(2) * qtyColl.Item(3)
                
            ElseIf qtyColl.Count = 4 Then
            
                rowResult = qtyColl.Item(1) * qtyColl.Item(2) * qtyColl.Item(3) * qtyColl.Item(4)
            
            End If
                        
            .Range("I" & i).Value = rowResult
            
            rowResult = 0
            Set qtyColl = Nothing
        
        Next i
        
    End With

End Sub

And here's a working example: https://1drv.ms/x/s!AnpELGec0aCql7lB2wPvYFIK1Bg75g

Wow! This is impressive. Thank you very much for your help. I'm not sure what it does completely, but I'm gonna try to understand a bit.

 

[dhman08]

Maybe I didn't do a good job of explaining what I'm trying to do and I apologize for that.

Vendor	Qty	Vendor	Qty	Vendor	Qty	Vendor	Qty
ABC		DEF		GHI	1	JKL
APL	2	SAM		MOTO	3	GOOGLE
BlackBerry		BlueBerry	2	OrangeBerry	2	GreenBerry

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[dhman08]

Maybe I didn't do a good job of explaining what I'm trying to do and I apologize for that.

Vendor	Qty	Vendor	Qty	Vendor	Qty	Vendor	Qty
ABC		DEF		GHI	1	JKL
APL	2	SAM		MOTO	3	GOOGLE
BlackBerry		BlueBerry	2	OrangeBerry	2	GreenBerry

<tbody>
</tbody>

Maybe I didn't do a good job of explaining what I'm trying to do and I apologize for that. So essentially what I'd like to do is come up with a formula that'll,

1) for each row check every other Qty column (starting with Column B) to see if the quantity for that vendor is greater than 0,
and
2) if Qty is greater than 0 it'll take that number, do the same check for the next column until it reaches the very last column, column H
and
3) once it reaches column H, it'll multiply all the non-zero columns and return the product of those Qtys. For example: if you look at row 2 (excluding header row)Column B is non-zero, column D is 0, Column F is non-zero, and column H is zero. So what I'd like the formula to do is ignore all the columns that have a value of 0 and multiple 2 and 3 (non-zeros) which yields to a product of 6

Vendor	Qty	Vendor	Qty	Vendor	Qty	Vendor	Qty	Total
ABC		DEF		GHI	1	JKL		1
APL	2	SAM		MOTO	3	GOOGLE		6
BlackBerry		BlueBerry	2	OrangeBerry	2	GreenBerry		4

<tbody>
</tbody>

 

[dhman08]

I may have figured it out:

Looks like this formula works

=PRODUCT(IF(B3:H3<>0,B3:H3))

Confirm with CTRL-SHIFT-ENTER to make it a array formula


I'll provide update after some trial and error

 

[Krayons]

Nevermind.

 

[dhman08]

Nevermind.

I think your code was fine as well, but I'm too dumb to understand that completely. Thank you for your help though. Much appreciated.