Jul 13 2019 06:33 PM
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 each area. Example, for area #1 Saw, the code "electrical" occurs 2 times. I know I could use a COUNTIF formula; however when I do this is, it is not very quick and doesn't work when I drag down to get the rest of the data. Any ideas of formulas that would work quickly for my data set?
Thank you.
Jul 13 2019 07:24 PM
Hi
Although I cannot clearly see the column headings in your screenshot but assuming that your first condition is in Column A (Starting from A2 to A500
and your second criteria range is in Column F starting from F2 to F500'
Then
You want to count based upon 2 conditions :
Options for Condition 1 are set in Column J starting from J6
Options for condition 2 is in Cell K5
Then in Cell K6 I will create a SUMPRODUCT function as follows:
=SUMPRODUCT(($A$2:$A$500=$K$5)*($F$2:$F$500=$J$6))
Hope that helps
Nabil Mourad
Jul 13 2019 07:54 PM
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:
Jul 14 2019 10:54 AM
Hi
Although in my reply I was sticking to your original setup, however I agree with @
That pivot tables are the shortest, easiest and fastest option to achieve the desired outcome.
Best of luck
Nabil Mourad
Jul 14 2019 11:45 AM
@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.
Jul 14 2019 12:16 PM
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)
Jul 14 2019 07:29 PM
use the countifs formula, you can enter multiple criterias your first criteria is your column on the left part, then the second criteria is your column heading. use absolute rows for your criteria range and your second criteria.
countif works just for single criterias countifs is best used for multiple-you need not enter complex ifs or array formulas
next time include a file that can be edited for clarificatory details