Adding numbers to formula for sum

dragonmouse

Board Regular
Joined
May 14, 2008
Messages
87
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?
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

samitnair

Board Regular
Joined
Jul 5, 2010
Messages
146
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

Board Regular
Joined
May 14, 2008
Messages
87
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,651
Messages
5,549,204
Members
410,905
Latest member
Extjel
Top