Adding numbers to formula for sum

[dragonmouse]

I have a spreadsheet that sums columns from multiple spreadsheets into one consolidated spreadsheet:

=IF(SUM('Week 28:Week 40'!M4)>0,SUM('Week 28:Week 40'!M4)," ") this formula works perfectly...however

I would like to add 2 columns to this formula so that if it calculates cells M4 across each spreadsheet and that total comes out to 7 (assuming a qty of 1 on each spreadsheet)....on my "consolidated sheet" (column A has 1 and column B has 1) I would like it to add a couple of more columns so that the total is 9.

Any guesses?

 

[samitnair]

I am pretty sure your we can help you answer your question but a screenshot of both the sheets would be helpfull to get a bit of clarity

 

[dragonmouse]

Thank you. For whatever reason I'm having problems uploading the screenshot. (I'm pretty sure it's on my end).

I've got another way of attacking this. Instead of "adding" I'm going attempt a formula that will:
Look at ALL three columns and if ANY of the columns: (A=Formula Column), (B=Input Column) or (C=Input2 Column) have a number greater than 0 just return TRUE otherwise FALSE

1. Column A =IF(SUM('Week 28:Week 40'!M4)>0,SUM('Week 28:Week 40'!M4)," ")=0, Column B=0, and Column C=1
2. Column A =IF(SUM('Week 28:Week 40'!M4)>0,SUM('Week 28:Week 40'!M4)," ")=2, Column B=0, and Column C=0
3. Column A =IF(SUM('Week 28:Week 40'!M4)>0,SUM('Week 28:Week 40'!M4)," ")=0, Column B=0, and Column C=0
4. Column A =IF(SUM('Week 28:Week 40'!M4)>0,SUM('Week 28:Week 40'!M4)," ")=3, Column B=2, and Column C=1
5. Column A =IF(SUM('Week 28:Week 40'!M4)>0,SUM('Week 28:Week 40'!M4)," ")=0, Column B=0, and Column C=5

RETURNS in Column D:
1. TRUE
2. TRUE
3. FALSE
4. TRUE
5. TRUE

In a nutshell Columns B and C are forecasts by the production controllers on how much equipment they expect to send in for the year. Column A is a weekly consolidation of everything that was ACTUALLY sent in (which includes items that were not forecasted). I'm not sure how to write the new formula but hopefully I'll figure something out. It might be easier than trying to force the formula to add (it keeps giving me a "value"). I just need something to indicate one of the 3 columns has info...I don't care what the info is. I'm trying to make sure if any of the 3 columns have info that I can filter them inclusively.