I need help with finding the correct formulas to use to help extract data based on specific information so I don't have to manually count the information out when I dump the raw data. I am trying to count how many times a specific "work center" that an operator clocks to goes over the planned hours. Then in two separate cells, calculate the sum of the "planned hours" and the "actual hours" for the specific work center the operator went over on. I have attached an example file of the data I collect and am trying to speed up the process of looking at each work centers performance.
Example data I gather:
I have "Termo" work center that exceeded the planned hours 55 times. This is manually counted in excel and entered into cell "B2".
The total planned hours for "Termo" work center that exceeds planned hours is 186.6 hours. This is manually counted in excel and entered into cell "C2".
The actual hours for "Termo" work center that exceeds planned hours is 272.85 hours. This is manually counted in excel and entered into cell "D2".
The data I am looking at is in the following cell locations:
"Termo" is in cell "G" with other work centers
Planned hours is in cell "J"
Actual hours is in cell "K"
I have tried =COUNTIFS(G:G,"termo",J:J,"<"&K:K ) formula with no luck and have searched far and wide to find a solution with no luck.
Oh, and I am using Microsoft Office Professional Plus 2013.
Hi Melissa
In cell B2 enter
=SUM(IF($K$18:$K$722>$J$18:$J$722,IF($G$18:$G$722=A2,1,0),0))
This is an array formula so you will need to press CTRL+SHIFT+ENTER instead of ENTER.
You can then copy it downwards till row 7
Hope this helps
Sunny
An alternative is to create a new data column to calculate any excess hours. I used Column O with the formula "=IF(K18>J18,K18-J18,0)" copied down.
i.e. Are there excess hours? If so, calculate those else enter 0.
B2 could then be: =COUNTIFS($G$18:$G$722,$A2,$O$18:$O$722,">"&0)
i.e. Count the number of instances where the Work center matches the name in Column A and where there are excess hours.
If you can add a helper column like what Paul suggested, that would be the best as it avoids all the complications of an ARRAY formula.
My formula would be =IF(K18>J18,1,0) in cell O18 since you only wanted to count the instances.
The formula in cell B2 would then be =COUNTIFS($G$18:$G$722,A2,$O$18:$O$722,1)
Sunny
I like Sunny's elegant solution. As a challenge to myself, I thought I would try to find an even shorter formula for O18 et seq.
How about "=--(K18>J18)"?
If you don't want a helper column, I'd suggest:
B2 =SUMPRODUCT(($J$18:$J$722<$K$18:$K$722)*($G$18:$G$722=$A2))
C2 =IF($B2>0,SUMPRODUCT(($J$18:$J$722<$K$18:$K$722)*($G$18:$G$722=$A2)),0)
and copy that across to D2, then fill down.
=(K18>J18)*1 will also work
