# IF/WHEN for when for cells greater than a

#### dhman08

##### New Member
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>

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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,"")

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.

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.

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.

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.

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

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

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.

Rich (BB code):
``````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

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.

Last edited:
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.

Code:
``````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

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

Replies
3
Views
302
Replies
6
Views
132
Replies
0
Views
150
Replies
3
Views
472
Replies
4
Views
365

1,196,391
Messages
6,015,006
Members
441,865
Latest member
Enragedpanda

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back