SumIf on multiple worksheets?

smashclash

Board Regular
Joined
Nov 24, 2003
Messages
126
Office Version
  1. 365
Platform
  1. Windows
I have a file that contains worksheets for every month and a YTD worksheet. The YTD worksheet is suppose to sum up all the data from the prior month worksheets. There are 2 columns on each month's worksheet - ACTUAL and BUDGET. The budget information is already filled in on every sheet for the year. Actual data is filled in as the months of the year pass. The problem I'm wrestling with is I want to the YTD file to only add information from the month worksheets if there is data in the ACTUAL columns. Right now I'm stuck with 12 months worth of BUDGET in my YTD file and its compared against only 2 months of ACTUAL data.

What I am trying to do is come up with a formula that will check cell E14 (the first cell that will contain ACTUAL data) in each worksheet to see if there is any data there. If there is data there, then add the data in cell G14 (the first cell that will contain BUDGET data). This YTD sheet needs to be able to look at all 12 month sheets and add them if there is data in cell E14. Does anyone have a suggestion for a formula that would work?

Below is a copy of my file:

 

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.

jyantiss

New Member
Joined
Feb 18, 2004
Messages
43
So you have 12 tabs of data... let's call them Sheet1,2,3,etc...
And on each tab you have two fields of data that you want summed on a summary page. We'll use cells A1 and B1 on the summary page, and Cells A1 and B1 on the 12 tabs of data.

Your summary page A1 can sum all 12 tabs because it will always be accurate as a user updates through the months. So you can write a basic formula to sum those.

Your summary page B1 needs If statements included in the formula:

=sum(if(Sheet1!A1=0,0,Sheet1!B1)+if(Sheet2!A1=0,0,Sheet2!B1)+if(Sheet3!A1=0,0,Sheet3!B1))

This would add the values in B1 only if the values in A1 are greater than 0.
You can finish the formula in the same manner until you have all 12 tabs in.

Hope this helps!
 

smashclash

Board Regular
Joined
Nov 24, 2003
Messages
126
Office Version
  1. 365
Platform
  1. Windows
I think I figured it out. The formula is incomplete for all months but I know it works for at least Jan and Feb sheets.

=SUM(IF(LEN(jan!E14),jan!E14,0),IF(LEN(feb!E14),feb!E14,0))

***EDIT, this is the correct code that i need:

=SUM(IF(LEN(jan!E14),jan!K14,0),IF(LEN(feb!E14),feb!K14,0),IF(LEN(mar!E14),mar!K14,0),IF(LEN(apr!E14),apr!K14,0),IF(LEN(may!E14),may!K14,0),IF(LEN(june!E14),june!K14,0),IF(LEN(july!E14),july!K14,0),IF(LEN(aug!E14),aug!K14,0),IF(LEN(sept!E14),sept!K14,0),IF(LEN(oct!E14),oct!K14,0),IF(LEN(nov!E14),nov!K14,0),IF(LEN(dec!E14),dec!K14,0))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

smashclash said:
I think I figured it out. The formula is incomplete for all months but I know it works for at least Jan and Feb sheets.

=SUM(IF(LEN(jan!E14),jan!E14,0),IF(LEN(feb!E14),feb!E14,0))

=SUM(N(jan!E14),N(feb!e14))

or

=SUM(jan:feb!E14)
 

smashclash

Board Regular
Joined
Nov 24, 2003
Messages
126
Office Version
  1. 365
Platform
  1. Windows
Aladin Akyurek said:
smashclash said:
I think I figured it out. The formula is incomplete for all months but I know it works for at least Jan and Feb sheets.

=SUM(IF(LEN(jan!E14),jan!E14,0),IF(LEN(feb!E14),feb!E14,0))

=SUM(N(jan!E14),N(feb!e14))

or

=SUM(jan:feb!E14)

What does the N mean?

I can't use the second formula because I only want it to add if there is data in a different cell.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

smashclash said:
Aladin Akyurek said:
smashclash said:
I think I figured it out. The formula is incomplete for all months but I know it works for at least Jan and Feb sheets.

=SUM(IF(LEN(jan!E14),jan!E14,0),IF(LEN(feb!E14),feb!E14,0))

=SUM(N(jan!E14),N(feb!e14))

or

=SUM(jan:feb!E14)

What does the N mean?

N(A1) returns 0 if A1 houses text. If A1 houses a true number, it returns that number.

I can't use the second formula because I only want it to add if there is data in a different cell.

Since you posted the following

=SUM(IF(LEN(jan!E14),jan!K14,0),IF(LEN(feb!E14),feb!K14,0),IF(LEN(mar!E14),mar!K14,0),IF(LEN(apr!E14),apr!K14,0),IF(LEN(may!E14),may!K14,0),IF(LEN(june!E14),june!K14,0),IF(LEN(july!E14),july!K14,0),IF(LEN(aug!E14),aug!K14,0),IF(LEN(sept!E14),sept!K14,0),IF(LEN(oct!E14),oct!K14,0),IF(LEN(nov!E14),nov!K14,0),IF(LEN(dec!E14),dec!K14,0))

to show your objective...

Install the free morefunc.xll add-in in order to use a formula with THREED:

After that, insert two new sheets, name them First and Last, put all the relevant month sheets in between First and Last, then use...

=SUMPRODUCT(--(THREED(First:Last!E14)<>""),THREED(First:Last!K14))
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
would changing this to an array sum formula change anything??..like...

{=SUM(--(THREED(First:Last!E14)<>"")*THREED(First:Last!K14))}

or am i looking at this wrong? just thought i'd ask. sorry to barge in.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
firefytr said:
would changing this to an array sum formula change anything??..like...

{=SUM(--(THREED(First:Last!E14)<>"")*THREED(First:Last!K14))}

or am i looking at this wrong? just thought i'd ask. sorry to barge in.

If you want to turn a SumProduct formula into a control+shift+enter'ed Sum formula, you need to drop explicit coercers for conditional expressions...

{=SUM((THREED(First:Last!E14)<>"")*THREED(First:Last!K14))}

or

{=SUM(IF(THREED(First:Last!E14)<>"",THREED(First:Last!K14)))}

But I don't see any need for it.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
well in your opinion, what would the benefit be for keeping a sumproduct formula versus a CSE sum formula? is it just that the array slows it down considerably? or is it just personal preference?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,655
Messages
5,597,384
Members
414,141
Latest member
Joey_T92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top