Excel Question

[slickp007]

Hi,

I have a SUMPRODUCT formula as below:

=SUMPRODUCT((Jan!$G$2:$G$150="Bedford") * (Jan!$K$2:$K$150 = "BOUND") * (MONTH(Jan!$O$2:$O$150) = 1))

However the guy i'm doing this for has come back to me and said he would like the month formula to cater for all the worksheets on the workbook, ie feb, mar, apr, etc. Do you guys have any ideas to incorporate this into this formula above or a better way in which i can stucture the workbook to make this easier to manage?

 

[AnotherHiggins]

Never, never, never break up like-data into multiple sheets based on date, department, etc.

You should consolidate all data into a single table on a single sheet. Add a column for Date.

If you structure the data properly, analysis and reporting are a breeze. You can put together a report showing quarterly, annual, year-over-year data, etc. in a few SECONDS.

That's opposed to the way the data is stored now. If someone asked for a quarterly report, you would - at best - have to take the time to put those three sheets together.

Trust us:
[tab]*Proper data storage = easy
[tab]*Improper data storage = horrible life spent scrambling around for answers

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[slickp007]

Unfortunately my boss doesn't seem to see it that way, so is there any way i can manipulate my SUMPRODUCT formula to incorporate all 12 sheets?

 

[SkipVought]

"Unfortunately my boss doesn't seem to see it that way..."

Unfortunately, your boss does not understand how Excel's features work. You can tell him that from me.

In order to fully engage Excel's GREAT features, your data needs to be in ONE table.

From that kind of sturcture, you can quite simply and easily, and as John has pointed out, in mere SECONDS, generate Weekly, Monthly, Yearly data REPORTS.

It is so much easier to go from a table to indivudula tabs of monthly data, that it is to aggregate from disparate tabs.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[xlbo]

The way to do what you want in your structure is:

=SUMPRODUCT((Jan!$G$2:$G$150="Bedford") * (Jan!$K$2:$K$150 = "BOUND") * (MONTH(Jan!$O$2:$O$150) = 1))
+
SUMPRODUCT((FEB!$G$2:$G$150="Bedford") * (FEB!$K$2:$K$150 = "BOUND") * (MONTH(FEB!$O$2:$O$150) = 1))
+ ....repeat for each month and for each variant of the formula....still want to keep your data all chopped up?

I'm 10000000% with Skip & John on this one. Never, ever, ever chop the data up into seperate tabs for each month / company / whatever

There is a MASSIVE difference between STORAGE of data and REPORTING on data

If your boss wants to see monthly sheets, fine - that can be achieved very easily - as long as your base data is all on one sheet. You can create a simple routine that creates the monthly sheets for you. In fact, as long as the base data is all on one sheet, you can do pretty much anything you want. Your boss doesn't need to know how you are storing the data - just how it is presented

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Tatertot45]

How does the spreadsheet get its' data? Maybe you could use a seperate tab to consolidate all of the tabs into one, then use yet another tab to summarize the data efficiently. I'm very familiar with your predicamant. A word of advice. "Start with the end in mind". You may not have all the tools you need to do this easily, so start with what you want to accomplish, and work backwards until you get to the starting point. then, move forward. You could hide all of the "old tabs" and just keep the current visible, in order to eliminate clutter.

Good luck...

 

[AnotherHiggins]

Well, you did ask:
Do you guys have any ideas to incorporate this into this formula above or a better way in which i can stucture the workbook to make this easier to manage?

I've run into this before, too. The problem is management knows how they want to see the data. That's fine. Those are called reports. That should have nothing to do with how the data is STORED.

If it is explained that improper (and that's what we're talking about here - doing things the wrong way, period) data storage can result in literally dozens - if not hundreds - of wasted man-hours each year, your manager might allow a change.

I've been in your spot. I know it isn't always easy to tell your manager she's wrong (even if it is sometimes fun ), but usually the manager doesn't realize that she is demanding that her employees work inefficiently.

Consider what will happen in January next year. Given your current storage methods, that month's data will be in a whole new workbook. I would explain to your boss that if you store data in the current manner, there is almost certainly going to come a time when they ask for a report that you cannot get for them in a reasonable time frame.

And please don't take our comments personally. We aren't attacking you. We are just trying to explain how very, very important it is to use Excel properly.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.