I'm trying to use the SUMIFS formula to total the number of shop hours each month out of the list of all the offers we've made. When I use the SUMIFS I keep getting 0 as the total. Can you help me figure out what I'm doing wrong?
I've attached a screen shot of my worksheet.
Nanaia

Attached Images

ExcelShot.PNG (37.6 KB, 42 views)

Attached Files

Fab Estimator Chart-test.xlsx (32.7 KB, 7 views)

Hello
could you please post your sheet on the forum so we can work with it? ( click " Go advanced" - Manage attachments)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

First off, I think you did a pretty good job on your Entry sheet. The table is Formatted As Table which is a functionality that too few people use and it's quite powerful.

On the next page, I like the SumIfs you did referring to the column and row headings of that table, Estimator and Date. However, you are kind of reinventing the wheel a bit so I am going to introduce you to your new favorite Excel thing: pivot tables!!!

Look at the attached workbook. I have recreated your table on sheet1 with a pivot table. It took me all of about 15 seconds to set up and once you get the knack of it, you'll bang them out that quickly too. They are wicked easy and, like Format As Table, quite powerful.

I have the dates grouped by month as you have, but could have easily grouped by quarters or days. Just right click on any of the Month headings and choose Group to see how it's done. Pro tip: you usually need to select both the year and the subgroup you want otherwise the pivot will group the Septembers and Octobers of every year together.

I think I got what you want on the last tab, Pivot2, but wasn't sure. Check it out and let me know. If you need any other help on this, just let us know. Cheers!

Attached Files

Fab Estimator Chart-test.xlsx (29.1 KB, 10 views)

The second page is exactly what I was looking for! I tried creating a pivot table but couldn't get it to pull the criteria I wanted. Can you explain how you got it to put it by month? My apologies if it's self explanatory. I've been dealing with poor network connections today!

I'm just packing up to travel home. I will put something in writing tonight for ya. Cheers.

When you're setting up the pivot table, just drag Date from the PivotTable fields into the columns box underneath. Once you've done that, you can adjust the grouping by right clicking on any of the column headers in the actual pivot table (Sep, Oct, Nov, etc. here) and selecting "Group...".

To have it group like this, you need to select Year and Month and click OK. Boom! You're done. Let me know if you still have questions.

Thank you! You've been incredibly helpful!

With the same spreadsheet - how do you use a pivot table to track the percentage of quotes that were awarded? Or am I wrong in thinking that can be done?

I'd love to learn more about this program. I'm disappointed there are in depth classes available any more. (at least not that I've been able to find... maybe I'm looking in the wrong place...)

Thank you again for all your help!

The percentage of the number of quotes or of the shop hours? Or something else? Do you want them as a percentage of the total for each month or of the grand total.

Any of these is possible, I think. See attached. I did percentage of shop hours by status for each month. All I did was add another Shop Hours to the Values section of the pivot fields. Click on it and select Value Field Settings and make sure it's a sum. Then go to the Show Values As tab and select % of Column Total. A lot of them are blanks which you can filter out if you don't want them to be in the calculation.

Attached Files

Fab Estimator Chart-test.xlsx (29.8 KB, 7 views)

I was wanting something more along the lines of what percentage were awarded/lost/unknown vs total number quoted each month.

Same thing only now count and summarize by status. See Pivot3.

Attached Files

Fab Estimator Chart-test.xlsx (32.8 KB, 9 views)

gebobs - you are AWESOME! And very patient! :-D Thank you.

Ready for another challenge? I feel I'm showing my inexperience again...
My goal here (or at least one of them...you're teaching me a lot!) is to be able to chart (line chart?) the number of shop hours quoted, and the number of shop hours awarded. So, for example Sept. had 3709.28 hours quoted, and 99 hours awarded. The pivot charts will enable me to view the data feeding the dashboard.
Hope that makes sense.

I'll put my mind to it asap. Just got back from vacation.

See Pivot4 in the attached. There is a chart which shows % Shop Hours Awarded. Let me know if this works for you. If so, then you can play with the chart formatting to prettify it anyway you like.

Basically, I just copied the pivot table in Pivot 2 and deleted the totals, leaving the percentages. I filtered out everything but the awarded amount.

Next I inserted a Pivot Chart (from the Analyze in Pivot table Tools) and created a line chart. I haven't worked with pivot charts much but they don't always do what you want. In this case, they were charting the percentage awarded with the month in the legend, basically the opposite of what is needed. So to change that, I right click on the chart and select "Select Data..." and press the Switch Row/Column button. If you do that, you'll see it actually changes the table too.

One nice thing about the pivot charts is that it has the filters right on the chart so you can modify what is shown directly on the chart.

Attached Files

Fab Estimator Chart-test.xlsx (47.4 KB, 9 views)