Need help with a formula in excel

[laport14]

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.

[#2]

@laport14 

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

[#3]

@laport14 

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:

1. Select any cell in the Table containing your data
2. Use the Insert...PivotTable menu item
3. 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
4. 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.

[#4]

@laport14 

Hi

Although in my reply I was sticking to your original setup, however I agree with @

Brad_Yundt

 That pivot tables are the shortest, easiest and fastest option to achieve the desired outcome.

Best of luck

Nabil Mourad

[#5]

@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.

[#6]

@laport14 

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)

[#7]

@laport14 

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