SUMIFS Function - Seeing the "Records" that make up a result

At the moment, I'm using the SUMIFS function to sum invoices that meet certain criteria to track a budget.

So far, so good.

I used to use PivotTables a lot - and liked the way that by double clicking on a PivotTable cell a new tab would be created showing the records that made up that PivotTable cell result.

Is there a similar way to see the "records" that make up a SUMIFS result? At the moment my spreadsheet is fairly easy and I can just about check the results manually - but I'd like to be able to drill down on the SUMIFS result to see which invoices make it up.

Maybe you could use conditional formatting to highlight the cells which contribute to the SUMIFS result. Attach a sample workbook as described in the yellow banner at the top of the screen, so we can see your data in context.

Hope this helps.

Pete

Hi Chemist B

Thanks for your suggestion - it definitely has it's uses but I am trying to be able to drill down whenever required to see which invoices make up each of the totals generated by SUMIFS.

Peter

Hi Jeff

Wow - that achieves exactly what I want. I'm inexperienced with macros - any ideas on how I could replicate the logic in your spreadsheet in my one?

Thanks for helping.

Peter

Hi Pete

Here goes - here is my spreadsheet.

Pete

Apologies - I meant to say thanks in my reply - but I pressed the submit button too quickly!

Thanks

Peter

Give us a clue. Where are the SUMIFS formulae located, and which cells do you want to highlight?

Pete

Hi Pete

The formula are on the "Budget Analysis" tab - e.g. cell E45. The underlying data being summed is on the "Invoices" tab - in the table. Hope this helps.

Hi Jeff

Thanks for looking into this - I'd be very happy if I could drill down from a SUMIFS results cell to see the underlying records that constitute that result - but don't stress if it is too difficult. I'm just saying thanks for trying in a roundabout way - hopefully you can adapt your macro reasonably easily. I'm slightly relieved that there wasn't an easier way of doing this that I had missed!

Peter

Hi Jeff

I think that I get your drift. It just seems very odd to me that Excel doesn't provide a facility to drill down on a SUMIFS result to see the underlying records. If I can't prove where a number comes from, I have my doubts about it being correct.

Thanks for your thoughts and help.

Peter

This is not a solution, but a way to give MS Excel Team feedback and suggestions.

From your Office app, go to Help > Feedback. From your Office app, go to File > Feedback. Use the feedback tool in the upper-right corner of your app to send feedback directly to our team. This will not bring immediate results, but if enough people like the idea, it could be added in a future update or version release of Excel.