Sum data in one tab and place value in anohter tab. 3

[roaml]

Hello,

I need a suggestion on how to get the following in a formula.

I would like to sum “days” associated with a particular manager. For example, I have raw data in Tab A and would like to sum the number of days (10) for “Mike Smith” and place the sum total in Tab B.

WO# Days Mgr
A1 2 Mike Smith
A2 5 Bill Tayor
A3 7 Mike Smith
A4 0 Sylvia Lake
A5 1 Mike Smith

Thank you.

 

[Gavona]

Have a look at Sumif().
It may be slightly easier to construct the formula on the first sheet then cut and paste it to the second.

A pivot table would also do the job very effectively. With manager as a pagefield you can select wich manager. Or as a row field you get a summary for each manager.


Gavin

 

[DaleWatson123321]

Another option: =DSUM(data,"Days",crit)

where:

A) "data" is a range name that you assign to your data. This range can include the entire columns if you like.
1) Just click on the column letters A,B,C) to highlight the entire columns.
2) Hold down <Ctrl> while you hit <F3> - This brings up the "Define Name" window.
3) Type: data
4) Hit <Enter>

B) "Days" is a reference to the field you want the database formula to SUM (based on the 3rd part of the database formula - the criteria)

C) "crit" is an arbitrary name - i.e. make up your own name. This name is a range name you need to assign to two cells:
1) The top cell needs to be the field name - in this case: Mgr
2) The cell immediately below needs to be the name (of the
Manager in this case) you want to base your addition on.

You could place this criteria on the same sheet as your data. HOWEVER, my strong recommendation is that you place all criteria on a SEPARATE sheet. Two Reasons: 1) It won't interfere with insertion/deletion of rows on your data sheet. 2) Excel can occasionally be confused because of the same field names (in the criteria) being used on the same sheet as the field names of your data.

Remember: The top row of your data needs to have field names - like the ones in your example.

The use of Excel's database formulas can "open up" a WHOLE new world. While Pivot Tables are useful, they can be more restrictive. With a little practice and experimenting with database formulas, you'll become amazed at the hidden power of this feature.

The criteria, for example, can be made as dynamic as required to analyze the data. You only need to use additional fields. And, you can use formula(s) on the 2nd row that reference 1 or more field names. Caution: If you do use formula(s), be aware that (in most cases) Excel requires that you do NOT use a field name in the 1st row - i.e. above where your formula resides.

One "tiny" example of hidden power... is that you can use the same criteria to extract the records related to the TOTAL value generated by your DSUM formula. A copy of these records can be extracted to a separate sheet and/or printed "at the click-of-a-button. And of course there are additional database functions DCOUNTA, DAVG, etc.

Hope this is "food for thought".

Regards, Dale Watson

 

[roaml]

Hi Dale,

Thank you so much for your overview. I am not that familiar with Excel formulas, but I am trying.

I was wondering if you can tell me what I am doing wrong with this formula?

=SUMIF(A1!F2:F5000="Mike Smith",A1!K2:K5000,D4)

Tab A1 F2:F5000 holds the Manager's name and A1 K2:K5000 holds the # of days that I am trying to calculate. D4 is on tab 2 and stores the sum of the # of days.

Thank you so much.

 

[Lilliabeth]

I'm not Dale, but I can help you.

Don't put the D4 in the formula.

Put the formula in D4.

Also, I think it is a bad idea to have a sheet with the same name as a cell. I would rename it.

Then select D4 and enter the following

=SUMIF('My Sheet'!F2:F5000,"Mike Smith",'My Sheet'!K2:K5000)

 

[roaml]

Yepeeeeeeeeeeeeeeeeeeeeeeee, it worked!!

Thank you so much Lilliabeth. I appreciate your prompt assistance.

 

[DeLaMartre]

Dale! You're back! (So am I...)

Thanks for the great tip. Another star to you.

Best Regards,


-Bob in California

 

[DaleWatson123321]

Thanks, Bob. ...MUCH appreciated.

...not just for the STAR, but your support and "endorsement" of the significant POWER of Excel's Advanced Filter. Hopefully this will help encourage others to "have a closer look".

On the surface, the Advanced Filter doesn't appear to be all that powerful, especially because Microsoft has provided VERY LITTLE in the way of built-in help and examples, and the examples they do provide are REALLY VERY BASIC - and MISLEADING.

By comparison, Microsoft has chosen to provide TREMENDOUS built-in help and examples for the use of Pivot Tables. While Pivot Tables are fine, they are certainly not the "be-all and end-all". The Advanced Filter component can be used to generate reports that are not possible using Pivot Tables.

I also notice Excel users using other functions such as SUMIF. Again, while this is an option, it does not allow for "dynamic" interaction of the user with the data - i.e. in terms of manipulation of the data and generation of various reports based on whatever user-input criteria the user requires in order to extract and/or print a wide variety of reports - at the "click-of-a-button"

One additional bit of advice for those wanting to explore the power of the Advanced Filter (database functions and data manipulation)...

I strongly recommend using field names that do NOT contain any SPACE characters. The reason is that when you get to creating more advanced criteria through the use of formulas, you will not be able to reference a field name that contains the SPACE character (even if you place quotes around the field name).

If you NEED to have headings that contain the SPACE character, simply insert a row below the heading row, and create your separate field names (without the SPACE character). Then simply hide this row.

One last note... There was a time when I was able to provide "living proof" for those Tek-Tips users who wanted to email me. I would then be able to easily "demonstrate" the POWER of the Advanced Filter while resolving their problem. Unfortunately, this is no longer an option because the posting of email addresses have become a problem at Tek-Tips due to spamming. Management also does not want users to take problems "off-line", but rather "share" the information with Tek-Tips members, which I can appreciate. However, it still leaves me somewhat "handicapped". I wish there was a realistic alternative. Oh well, maybe sometime in the future (perhaps when I retire) I'll be able to develop and maintain a website.

Hope this helps.

Best Regards, Bob.

Dale Watson

 

[DeLaMartre]

Thanks very much for the fine dissertation, Dale. I have been teaching Excel workshops in our company for the last couple of years, and your reply has inspired me to include future episodes that will reference DSUM, etc.

Have you ever used Captivate software to illustrate an Excel lesson? You can create interactive Flash movies with the application, and it is very easy to use. I have made several tutorials using this approach.

I appreciate your altruistic support of this forum, Dale. You are a fine man.

By the way, it is 75 and sunny here in San Diego today, what is it like in Canada?

-Bob in California

 

[Lilliabeth]

I also notice Excel users using other functions such as SUMIF. Again, while this is an option, it does not allow for "dynamic" interaction of the user with the data

I think learning is a bit like building a brick wall. You start with the bricks at the bottom and use those to support higher level bricks. A user who is unable to accomplish a task similar to the original poster's needs some bricks on the bottom. Every Excel user should have the Sumif brick.

 

[DaleWatson123321]

Bob,

No, I haven't used Captivate. I appreciate your mention of it though, as I might get to make use of it in the future.

I say in the future because my current day job doesn't involve working with Excel - at least not at the same "developmental" level as in the past. My current job is almost entirely administrative.

Ahhh the weather... Winnipeg is normally considerably colder than other parts of Canada. However, this winter has been quite mild. In fact we just set a record for the warmest January ever recorded.

Lilliabeth,

I accept your point and agree with your analogy of building the brick wall.

However, please appreciate my point - that Microsoft has effectively "hidden" the "Advanced Filter brick" from Excel users.

While Microsoft has provided some "un-hidden" information, it is VERY SPARSE - and MISLEADING. It's MISLEADING because of the MISSING information that needs to explain how to achieve the same dynamic manipulation of data that was possible with spreadsheets since the early 1990's.

At that time, Lotus 123 had extremely powerful Database functionality - to the point where it was even possible to create relational database tables. Such is still NOT possible with Excel - at least not to my knowledge. I'm sure someone will correct me if this is not accurate.

Bottom line... Once anyone can show me reports generated by Pivot Tables that come close to resembling the dynamic and professional looking reports I easily created in the past - and can still create today using Excel's Advanced Filter, then I will gladly "bury the Advanced Filter brick".

In the meantime, I sincerely believe it would be VERY appropriate for Microsoft to include an enhanced version of the Advanced Filter (database functionality) - especially in light of their announced new version of Excel scheduled for release this year. This new version will allow for 1,024,000 rows per sheet.

The Advanced Filter functions are MUCH faster (in calculation time and data manipulation) than the other functions. And this speed difference will become more important when it comes to manipulating data of up to 1 million records.

Regards, Dale Watson