# SumIf on multiple worksheets?

#### smashclash

##### Board Regular
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:

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### jyantiss

##### New Member
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
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))

##### MrExcel MVP
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
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.

##### MrExcel MVP
smashclash 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
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.

##### MrExcel MVP
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
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?

Replies
5
Views
227
Replies
0
Views
234
Replies
1
Views
147
Replies
0
Views
821
Replies
3
Views
405

1,181,785
Messages
5,932,039
Members
436,816
Latest member
Composh

### 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?

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