Forum Discussion
Need help with a formula in excel
I wish you had posted some sample data along with the formulas you are complaining about rather than a screenshot.
That said, you have posted a problem that is well-suited for solution using a PivotTable. PivotTables only calculate when you rightclick and choose Refresh. So they are much less calculation intensive. If you insist on using formulas, since you are wanting to count the number of incidents for each combination of Code and Area, you will need COUNTIFS formulas rather than COUNTIF.
The first step in creating a PivotTable is to move your existing data into a Table. You can do this by clicking any cell, then using the Insert...Table menu item. Make sure to check the box for My data has headers.
If you might want to calculate the lost time for each combination of Code and Area, you should add a column to your table with a formula calculating the lost time for that row. A PivotTable can sum up the lost time as well as count the number of incidents.
To build the PivotTable:
- Select any cell in the Table containing your data
- Use the Insert...PivotTable menu item
- The default is to put the PivotTable on a newly inserted worksheet, but I prefer to put it on the same worksheet as my data
- You will now be confronted with an unfamiliar task pane. Take a deep breath, then drag Code into Row items, Area into Column items, and Area into Value items.
Brad_Yundt Thank you for your response, here is the sample data set in question. I appreciate your quick response in reference to my question.
Since you have slicers already in your workbook, I now think it better to use COUNTIFS formulas to build your report. They are faster than SUMPRODUCT and can use entire columns without causing delays in recalc time. With your sample data, they recalculate in the blink of an eye when I use one of the slicers.
To permit the slicers to affect the count results, I suggest adding a column to your Table1. This column will return 1 if the row is visible or 0 if not. I used =SUBTOTAL(3,[@Area]) as the formula for this column.
The required COUNTIFS in cell L6 is then:
=COUNTIFS($F:$F,$K6,$A:$A,L$5,$I:$I,1)
