Forum Discussion

Amyhosh11's avatar
Amyhosh11
Copper Contributor
Sep 13, 2020

Excel Dashboard for Invoices

Hi,

 

I wanna prepare an interactive dashboard using SUM IFS formula in attached sheet. But when applying the formula, I'm getting #Value / 0 result. Basically, I want to find out Overdue invoices for all customers, with overall view (management view) on all the invoices to be tracked so that it gives me all the inputs from base sheet i.e overdue invoices, not overdue invoices, account name, aging, month wise & year wise with all the details. But when I tried for many times, I'm not getting the result as wanted

Could you please help me on this in attached sheet.

 

Regards,

 

Amy

excel
Formulas and Functions

1 Reply

  • Craig Hatmaker's avatar
    Craig Hatmaker
    Iron Contributor
    Sep 16, 2020

    Amyhosh11 ,

    Please see attached.

     

    To produce interactive dashboards we must use PivotTables, Slicers and Pivot Charts. The PivotTables do the equivalent of SUMIFS with a lot less effort. Their amounts can also be double clicked to drilldown into the raw data (not possible with SUMIFS).

    Slicers provide interactivity. They filter the PivotTables by whatever is in the data. And the Pivot Charts will respond to however the slicers have filtered the data. 

     

    Attache is a quick example. Hope that helps

Resources