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:

 
firefytr said:
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?

There are enough threads in which this issue has been discussed...

First things first:

SumProduct formulas and control+shift+enter'ed formulas both operate on (pre)computed arrays.

SumProduct formulas, all things being equal, in most situations are faster and certainly not slower than control+shift+enter'ed (CSE) formulas. It's just incroyable to suggest

{=SUM(A1:A10*B1:B10)}

while

=SUMPRODUCT(A1:A10,B1:B10)

is way faster and immensely suited for the "vector" multiplication.

CSE'ing proves difficult for most users of Excel. You need CSE also after every edit action. Hence, all those questions of the type "Why do I get #VALUE! error?"

Does this mean that CSE-formulas should be ditched? Certainly not. There is no formula option when filtering with IF is required along with computing arrays.

BTW, knowing one type implicates knowing the other.
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
okie dokie, thanks! i figured as much. although i would sometimes tend to disagree with your last comment, mostly from personal experience. but nonetheless, thank you. i always appreciate a lesson from aladin! (y)
 
Upvote 0
firefytr said:
...although i would sometimes tend to disagree with your last comment, mostly from personal experience. but nonetheless...

I said "...knowing one type implicates knowing the other" where I should emphasize "implicates". Human cognition seems to need a series of cold restarts (deliberation) to take advantage of what is in the closure of what it already knows. The most amazing example is learning of a foreign language. One starts, it seems, as if a toddler, while one is proficient in some natural language.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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
Back
Top