Good afternoon! (Or not in my case as excel is frustrating me)

I am a self proclaimed intermediate level excel user and a relatively simple sounding task has made my afternoon particularly unproductive and googling has not been fruitful. I would be very grateful if you could help!

My spreadsheet relates to staff briefing talks. I have columns for the person delivering the talk, the topic, the number of people the talk was delivered to and the month in which it was delivered.

I am trying to sum the number of people that recieved the talk of each topic and feed the data into a graph automatically.  I have done this successfully using the sumif function however the columns need to be filtered to extract data on a monthly basis excluding previous months. Sumif counts the cells that have been filtered out. I am trying to therefore use subtotal (with option 9 (sum)) to get around this but cannot find a way of combining it with an if statement or other function to get the totals of all topics at once displayed on a separate sheet when only filtered by month and without deleting data from previous months.

If that makes any sense to any of you and you can help me out, I (and my blood pressure) will be forever thankful!

Many thanks!

David

Can you use the date delivered as one of your data entries

pivot chart?  Date format might be the undoing of it though.

Make a cell with the reporting date somewhere and reference it as one of the sumif conditions (you can use SUMIFS for multiple conditions).

What Kelron said

Something like

=SUMIFS(WHHT!X$5:X$98,WHHT!AQ$5:AQ$98,$B10,WHHT!J$5:J$98,$G$6)

with the dates and types in B10 & G6

The pivot table could work if the graph can update automatically from it. Sumif can’t be used because the filtering of the data won’t be taken into account in the results. Therefore a combination of subtotal and if (or similar) would be ideal. Needs to be used by old dogs that struggle learning new tricks. Meaning they can filter but not much else

Pivot table with a Pivot chart over it.  When the data in the source is changed you’ll just need to click on Data->Refresh All to update the pivot table and chart, then use the date filter (group it when you set it up, it should deal with new dates from then on) to pick the month(s) that you want to see

I’m not sure I understand why you can’t use sumif? If it’s just being filtered to show current month then you include the month as a criteria in the formula, so you don’t need to filter and it will only look at the dates you feed it.

If you wanted to make it as simple as possible for other users you could include a month-drop down so they can choose which to look at, and reference it in the formula.

If people are going to be regularly filtering on different criteria, then yes a pivot table is the best option.

Pivot Table + Pivot chart.

make sure your original data is defined as a range e.g. DATA. then when you crete the pivot table point it at the range rather than a specific set of cells. That way when you data grows you can redefine the range once and then every pivot table that works off it will update automatically.

You’ll end up with something like this

Sumif doesn’t work with multiple criteria which is effectively what you want here.

You need to look at using sumproduct and the double unary.

I even put the post up in the forum moons ago about it..will search and see if I can link