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]

From my perspective, the simplest arrangement and easiest to understand, would be to place all your vendors in column A, Quantity in column B, Price or Cost in Column C, and the extended value (formula) in column D.

Formula in D2, copied down:

=IFERROR(B2*C2,"")

 

[dhman08]

From my perspective, the simplest arrangement and easiest to understand, would be to place all your vendors in column A, Quantity in column B, Price or Cost in Column C, and the extended value (formula) in column D.

Formula in D2, copied down:

=IFERROR(B2*C2,"")

I definitely agree with you and that's what I would've preferred as well. Unfortunately, this is a running file that I get from another department and they don't want us to mess around with the file. So I'm kinda stuck with it.

 

[Krayons]

1) Out of curiosity, why are you multiplying the quantities? I would think you should be adding them no? If Google has a quantity of 2, and Apple has a quantity of 3, you have a total quantity of 5. However, if you multiply the two, you've got a quantity of 6. Are you certain that is what you want to be doing?

2) Please explain where you want these results to appear. Is this proposed formula to be used in ONE column only, if so where, or in each of the columns that contain the quantities? If its in each of the columns containing the quantity, i don't think that'll be possible as a cell cannot contain both a value and a formula at the same time.

3) Are there only 4 columns that contain quantities?

Need more details. Thanks.

 

[KenDue]

Do you return the amended file back to the other department, or do you just append the new info from that department into your own master file?

If you will be adding thousands of cells to your file, you may have to convince the other department of the folly of having multiple vendors on the same row and the nightmare it will be to sort, formulate totals, and report values. A database spreadsheet approach is the best solution for maintaining your data, IMHO.

 

[dhman08]

1) That's what I thought too, bur according to my mentor it needs to be multiplied. He'll tell me once I'm done. I'm just an intern btw haha.

2) I think for now it doesn't matter where they appear, but as the spreadsheet start getting bigger my mentor will move the cumulative result to appear on the first row moving all the data a row down.

3) For now there are only 4, but it'll be like a running database. That's why I can't put all the data in one column. That would've made things much easier

Thank you for taking the time to reply btw.

 

[dhman08]

Do you return the amended file back to the other department, or do you just append the new info from that department into your own master file?

If you will be adding thousands of cells to your file, you may have to convince the other department of the folly of having multiple vendors on the same row and the nightmare it will be to sort, formulate totals, and report values. A database spreadsheet approach is the best solution for maintaining your data, IMHO.

For now my mentor wants me to return the file to the other dept and unfortunately he doesn't want me to change the format. Otherwise that would've made life much easier for me instead of trying to figure out how to accomplish this task

 

[FDibbins]

I agree that multiplying different quantities together makes no sense, but it's your file

Can you show some sample answers please?

 

[Krayons]

Thinking about this, I don't see how this can be accomplished with just a formula; especially if this data range will grow larger. Best achieved, in my opinion, through VBA. Also this poses a problem:

I think for now it doesn't matter where they appear, but as the spreadsheet start getting bigger my mentor will move the cumulative result to appear on the first row moving all the data a row down.

In my head, it's impossible to have answers laid out like that. If you are supposed to be calculating the result for multiple vendors that appear on each individual row, then you cannot have the results on just one row above all the data. Results will have to be in a column to the right of all the data.

I also cannot stress enough how wrong the math is here lol.

I mean, if all 4 of these vendors have a quantity, your going to end up with whack results.

EG: you have 4 quantities: 1, 4, 7, 8 then your result will be 224. I would urge you to double check with your mentor; maybe he's had a brain fart and meant to say add.

Either way, this is as much as I was able to come up with. I cannot figure out a way to collect the values, then multiply them all together. Maybe someone else can figure this out.

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
            
            For x = 1 To qtyColl.Count
            
                rowResult = x * x
            
            Next x
            
            .Range("I" & i).Value = rowResult
            
            rowResult = 0
            Set qtyColl = Nothing
        
        Next i
        
    End With

End Sub

Edit: Above runs through each row, but math is not correct. Refer to code highlighted in red. Having trouble figuring that out.

Good luck.

 

[Krayons]

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