Formula to overwrite a total if less than a total in another cell

[tammybc]

Hi,

I don't know the correct terminology... sorry. I am stuck.

Cell (L11) tells me how many more sales are needed to reach our sales goal for the month. Total changes daily. Goal Sales - sales to date = sales needed to reach goal.

Cell (H10) tells me our fixed weekly target for the month. Total never changes.

Cell (L10) When the total in H10 is greater that the total in L11, I want L10 to show the lesser amount, the true sales needed for the week, to wrap up the month. There may only be two business days left in the week, I don't want to tell the team that they have to meet a 5 day sales target with only 2 days left in the month.

L10 ... If the weekly target was fixed at 50,000 but there are 3 working days left in the month, and we are ahead of our sales goal, we only need 4000 more sales to meet target, I want 4000 to show in cell L10 and NOT the 50,000 in H10.

Thank you
Tammy

 

[SanjayGMusafir]

Hi,

I don't know the correct terminology... sorry. I am stuck.

Cell (L11) tells me how many more sales are needed to reach our sales goal for the month. Total changes daily. Goal Sales - sales to date = sales needed to reach goal.

Cell (H10) tells me our fixed weekly target for the month. Total never changes.

Cell (L10) When the total in H10 is greater that the total in L11, I want L10 to show the lesser amount, the true sales needed for the week, to wrap up the month. There may only be two business days left in the week, I don't want to tell the team that they have to meet a 5 day sales target with only 2 days left in the month.

L10 ... If the weekly target was fixed at 50,000 but there are 3 working days left in the month, and we are ahead of our sales goal, we only need 4000 more sales to meet target, I want 4000 to show in cell L10 and NOT the 50,000 in H10.

Thank you
Tammy

Upload your data sample using XL2BB utility and explain what you want to achieve. That way it becomes easy to understand and give a solution. But what I could understand from your information can be done using simple formula.

 

[tammybc]

Sanjay,

Thank you for your reply. I cannot upload the data, confidential.

I have a Sheet/tab named "PSales" with raw data. From that data I want to extract a total sales amount based on a specific Date range, a Product name and Sales total for Product in date range, to a new Sheet/tab (in the same workbook) named "RevExpPlan". PSales has 7,000 lines of data with headers in line 1.

Data Sheet named PSales using the following columns:
DATE in column C (date format: mm/dd/yyyy)
Product name in column E (contains multiple product names)
Country in Column H (contains multiple countries)
Sales total in column J

From PSales, I want to determine the sales total for the USA for the product named "CT5" in Aug 2021. (between 7/31/2021-9/1/2021)

I want the result to populate on worksheet "RevExpPlan" in cell M2.

I hope this information is clear and helpful.

Thanks again!

Tammy

 

[tammybc]

Sanjay,

Thank you for your reply. I cannot upload the data, confidential.

I have a Sheet/tab named "PSales" with raw data. From that data I want to extract a total sales amount based on a specific Date range, a Product name and Sales total for Product in date range, to a new Sheet/tab (in the same workbook) named "RevExpPlan". PSales has 7,000 lines of data with headers in line 1.

Data Sheet named PSales using the following columns:
DATE in column C (date format: mm/dd/yyyy)
Product name in column E (contains multiple product names)
Country in Column H (contains multiple countries)
Sales total in column J

From PSales, I want to determine the sales total for the USA for the product named "CT5" in Aug 2021. (between 7/31/2021-9/1/2021)

I want the result to populate on worksheet "RevExpPlan" in cell M2.

I hope this information is clear and helpful.

Thanks again!

Tammy

This is a new problem, although I have not remedied the other problem yet.
thanks again
Tammy