SumIf on multiple worksheets?

[smashclash]

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:

 

[Ekim]

Some ideas here:
http://www.mrexcel.com/board2/viewtopic.php?t=60091&highlight=sumif+multiple+worksheets


HTH

Mike

 

[jyantiss]

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]

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]

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]

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]

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]

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]

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]

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?