Forum Discussion
Help with IF
I need help making a IF function. We sell RVS and I made an Audit report stating what we have but when one sells I have a sold date. How do I make the IF formula for this? I need the Inventory Amount to go down with the sale. So if there is a sale date the amount in the $ will be deducted from the Sum function already there. IDK can someone help?
You can create a single formula to calculate the total inventory amount by subtracting the sale amounts for items with sale dates. You can use the SUMIFS function to achieve this. Here's how:
Assuming you have your RV inventory data in columns A (RV Model), B (Inventory Amount), and C (Sale Date), and you want to calculate the total inventory amount in a single cell (e.g., in a cell at the bottom of your inventory list), you can do the following:
- In a cell (e.g., cell B10), enter the following formula to calculate the total inventory amount:
=SUM(B2:B9) - SUMIFS(B2:B9, C2:C9, "<>")
Here is what this formula does:
- SUM(B2:B9) calculates the sum of all inventory amounts in the range B2:B9 (adjust the range to cover all your inventory items except the total cell).
- SUMIFS(B2:B9, C2:C9, "<>") calculates the sum of inventory amounts in the same range (B2:B9) where the corresponding sale date in column C (C2:C9) is not empty (i.e., there's a sale date).
- Subtracting the second sum from the first sum effectively subtracts the sale amounts from the total inventory.
Now, when you update your inventory and add sale dates, the total inventory amount in cell B10 will automatically reflect the correct total after deducting the sold items. Just make sure to adjust the cell references (B2:B9 and C2:C9) to match the actual range of your inventory data. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark them as helpful and like it!
This will help all forum participants.
To create an IF formula in Excel that deducts the sale amount from the inventory amount when there is a sale date, you can use the IF function in combination with other functions like SUMIF or SUMIFS. I will provide you with a step-by-step guide:
Assuming you have your RV inventory data in columns A (RV Model), B (Inventory Amount), and C (Sale Date), and you want to calculate the updated inventory amount in column D, you can do the following:
- In cell D2 (or the first cell in column D where you want to display the updated inventory amount), enter the following formula:
=IF(C2<>"", B2, B2-SUMIF(C:C, "<>"&"", B:B))
Here is what this formula does:
- C2<>"" checks if there is a sale date in cell C2.
- If there is a sale date (i.e., C2 is not empty), it simply returns the original inventory amount (B2).
- If there is no sale date (C2 is empty), it calculates the updated inventory amount by subtracting the sum of all inventory amounts where the sale date is not empty.
- Press Enter to calculate the updated inventory amount for the first RV.
- Drag the fill handle (the small square at the bottom right corner of the cell) down to apply the formula to the rest of the RVs in your list.
This formula will update the inventory amount by deducting the sale amount (from column B) for each RV when a sale date is present in column C. If there is no sale date, it will leave the inventory amount unchanged.
Make sure that the column references (A, B, C) match the actual columns in your Excel spreadsheet.
The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark them as helpful and like it!
This will help all forum participants.
- Is there a way to have the total inventory Report total = the sum off all amounts and subtract any with sale dates. So there is one formula on that line?
You can create a single formula to calculate the total inventory amount by subtracting the sale amounts for items with sale dates. You can use the SUMIFS function to achieve this. Here's how:
Assuming you have your RV inventory data in columns A (RV Model), B (Inventory Amount), and C (Sale Date), and you want to calculate the total inventory amount in a single cell (e.g., in a cell at the bottom of your inventory list), you can do the following:
- In a cell (e.g., cell B10), enter the following formula to calculate the total inventory amount:
=SUM(B2:B9) - SUMIFS(B2:B9, C2:C9, "<>")
Here is what this formula does:
- SUM(B2:B9) calculates the sum of all inventory amounts in the range B2:B9 (adjust the range to cover all your inventory items except the total cell).
- SUMIFS(B2:B9, C2:C9, "<>") calculates the sum of inventory amounts in the same range (B2:B9) where the corresponding sale date in column C (C2:C9) is not empty (i.e., there's a sale date).
- Subtracting the second sum from the first sum effectively subtracts the sale amounts from the total inventory.
Now, when you update your inventory and add sale dates, the total inventory amount in cell B10 will automatically reflect the correct total after deducting the sold items. Just make sure to adjust the cell references (B2:B9 and C2:C9) to match the actual range of your inventory data. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark them as helpful and like it!
This will help all forum participants.
