Forum Discussion

Deleted's avatar
Deleted
Jul 24, 2018

Adding a fixed number only if the next cell has a value

Hi 

I'm building a sales/ productivity tracker.

I would like to add the sales goal to the month end, only if there is a value beside it.

Example:

This is my formula for every Monday Sales Goals in a 5 week workbook. These goals are fixed and auto-populated.

='Week 1'!B6:B7+'Week 2'!B6:B7+'Week 3'!B6:B7+'Week 4'!B6:B7+'Week 5'!B6:B7

 

I would like to have the sales goals total together ONLY when there is data entered into the sales achieved cells

Example of Sales Achieved rolling total:

='Week 1'!C6:C7+'Week 2'!C6:C7+'Week 3'!C6:C7+'Week 4'!C6:C7+'Week 5'!C6:C7 

 

I hope I was clear enough.

 

Thank you

Scott

 

excel
Formulas and Functions

1 Reply

  • pranav trikha's avatar
    pranav trikha
    Brass Contributor
    Jul 30, 2018
    Greetings! Attached please find workbook conditions met a. Five weeks Goal and Achievement Sheets. b. Consolidated Summary Sheet. c. Sales achieved cells (C7 in sheets Week1 to Week 5) only accept data <= Goal Cells(B7 in Sheets Week1 to Week5) d. Summary Sheet (Total Sales Goal(Month) only add up when data is present in each Sales achieved cells(C7 in sheets Week1 to Week 5). e. The total Sales Achieved cell in Summary Sheet will show total only if above condition "d" is met. Thanks,

Resources