Formula to Sum Values from a Column Based on Multiple Criteria

[MaxwellR]

I didn't expect to have so much trouble and I may just be missing something but I'm stuck!

I have attatched an example workbook with a tiny subset of data and a number of criteria from the Dashboard Engine page removed.

What I need is to sum the total value for each division on the second sheet based on the date criteria (so for the first cell Jan 1/2010 - Jan 31/2010) and on a match between division name on the Dashboard Engine sheet table and the Masterdata sheet. I have tried a number of variations but keep getting a 0 for each return.

[Olly]

Use a pivot table. Use [Date] grouped by Year / Month as Column field, [Division] as Row field, [Sum of Amount($)] as Values field.

[Olly]

Here, try the attachment to see how it could work.

Full marks for structuring your source data nicely!

[MaxwellR]

I have zero experience with pivot tables so this is a learning opportunity. Thanks Olly. However, I can't get it to sort monthly. When I expand the year columns I have individual days as opposed to monthly totals. I see the "group" option but it is asking me to enter values. Do I have to manually enter the datevalue for each date range? I assume there is a much easier alternative staring me in the face.

[Olly]

In the Group dialog box, leave both 'Auto' checkboxes ticked to group the whole date range, then select Years and Months in the 'By' list. This should group ALL your dates by Year and Month

[MaxwellR]

A little lost, I apoligize. If I select the entire "date" row (all the cells with a date) and right-click and group it just autogroups them into "Group1" with no subdivision and no prompt box. If I select all the dates in one month (or any number of cells less than the entire row) I get the following prompt box:

Sample.png

[Olly]

Aha, yes - format your Dates (Masterdata, Column H) as a date numberformat, then the pivot table will treat it as a date field, and allow grouping by year, month, etc

[MaxwellR]

I tried that first. Masterdata column H is and has been in a number format which is why I'm very confused. Now, Masterdata column G [Year] is not because I'm not sure how to format just a year as a date column. However, the years are sorted fine so I'm not sure this matters. Is it possible excel doesn't see consecutive dates and therefore doesn't want to group them by month? Somedays there is no overtime taken hence gaps in the date sequences.

I'm so close but still a step away.

[Olly]

I tried that first. Masterdata column H is and has been in a number format which is why I'm very confused. Now, Masterdata column G [Year] is not because I'm not sure how to format just a year as a date column. However, the years are sorted fine so I'm not sure this matters. Is it possible excel doesn't see consecutive dates and therefore doesn't want to group them by month? Somedays there is no overtime taken hence gaps in the date sequences.

I'm so close but still a step away.

I think you misunderstand me - you need to format column H as a DATE format. It is currently formatted as a number.

Nonconsecutive / missing dates won't matter; once that column is formatted as DATES, the pivot table will treat them as dates, and you'll be able to group by year / month etc.

[MaxwellR]

Sorry I misstyped. Column H is a DATE format.

[Olly]

Sorry I misstyped. Column H is a DATE format.

In the sample you originally posted, column H was formatted as numbers. I reformatted as dates (dd/mm/yy) then created the pivot table - see my attachment above, and look at the grouping of the date field.

If you are still struggling, can you post your actual workbook, rather than a copied sample, so I can see your real data?

[MaxwellR]

Got it. The key was I had to recreate the table after I changed the column format to DATE. I assumed, wrongly, that changing it would auto update the formating of the pivot row. Thanks again for all your help Olly. You've taught me tons in the last two days!

[Olly]

Got it. The key was I had to recreate the table after I changed the column format to DATE. I assumed, wrongly, that changing it would auto update the formating of the pivot row. Thanks again for all your help Olly. You've taught me tons in the last two days!

Brilliant - pleased you got it to work, and really pleased you've learned a little along the way. Pivot tables can be excellent, and it looks like a perfect way of reporting on your structure of source data