Forum Discussion
Need help with a formula in excel
Greetings, I need help writing a formula. Attached is a screen shot of my data in excel. What I am trying to accomplish is to have excel return the number of times a specific code occurs under ea...
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)