formula array???

[cruch9]

I need help on how to do a function. Not even sure what function to use. I am trying to keep track of production percents per shift. I get daily production percents from each operator daily. I am going to enter either 1, 2 or 3 for what shift they were on and the %. On a seperate sheet I want to have it average all 1st shift in one cell and so on with 2nd and 3rd. Hope someone can help.

I have tried this and it wont work: =AVERAGE(IF(sheet1:sheet20!B6:B36)=1,(sheet1:sheet20!D6:H36))

thanks!

 

[Airfix9]

=SUMIF(Sheet1!B6:B36,1,Sheet1!D6:H36)/COUNTIF(Sheet1!B6:B36,1)

 

[GlennUK]

=SUMIF(Sheet1!B6:B36,1,Sheet1!D6:H36)/COUNTIF(Sheet1!B6:B36,1)

I don't believe that'll work for a 3D range. Am thinking about alternatives.

 

[Airfix9]

Ah, depends if the divisor is the number of times 1 appears or the number of percentages entered....

If the latter, we know it's 5 columns so multiply the divisor by 5 in this case, surely?

 

[cruch9]

will sum work if I am wanting a average?

 

[cruch9]

I need the result to be on one page, while I have 20 different sheets with the data on it.

 

[Airfix9]

I need the result to be on one page, while I have 20 different sheets with the data on it.

Ah, yes, didn't see the sheet20 bit, Glenn's right, it's 3D. Are you happy with VBA?

Also, is the divisor the number of percentages or the number of times the shift number is found?

 

[cruch9]

Thanks for the help so far! VBA?? not sure what you are talking about. It would be the number of "%" since there are up to 5 "%" per day/shift.

Thanks

 

[Airfix9]

OK, here's a UDF I created for you.

Open the Excel file and press ALT+F11

Then, find the Project Explorer on the left, if it isn't there, press CTRL+R to bring it up.

Find your workbook and expand the tree.

Right click the workbook and select "Insert... Module"

In the window that opens, paste this:

Public Function MYSUMX(fs As Integer, ls As Integer, fr As Integer, lr As Integer, iFind As Integer)
    Application.Volatile
    Dim s As Integer, r As Integer, c As Integer
    Dim subSum As Double, subCount As Double
    subCount = 0
    subSum = 0
    For s = fs To ls
        
        For r = fr To lr
            
            If Sheets("Sheet" & s).Cells(r, 2).Value = iFind Then
                For c = 4 To 8
                    subSum = subSum + Sheets("Sheet" & fs).Cells(r, c).Value
                    subCount = subCount + 1
                Next c
            End If
            
            
        Next r
        
        
    Next s
    
    MYSUMX = subSum / subCount
    
    
End Function

Then go back to the workbook.

In any cell, type this:

=mysumx(1,20,6,36,1)

To explain the parts:

(1 = the first sheet number "Sheet1"
,20 = the last sheet number "Sheet20"
,6 = the first row number
, 36 = the last row number
,1) = the shift number to look at

You need a macro-enabled workbook and the sheets must be named "Sheet1", "Sheet2" etc. If they are named differently, come back to me.

 

[cruch9]

wow that is complicated!!!