# Help with MAXIFS and MINIFS

#### JARichard74

##### Board Regular
The following formula seems to work. =MINIFS(F4:X4,\$G\$7:\$Y\$7,"COMPLIANT"). In the range F4:X4, I want to find the MIN of F4,H4,J4,L4,N4,P4,R4,T4,V4,X4 if the condition applies. (I'll want to do the MAXIFS as well)
I use the following VBA to add rows to the table
VBA Code:
``````Do Until z = 0
tblRow.Range.Offset(-1).Copy
tblRow.Range.PasteSpecial xlPasteFormulasAndNumberFormats
z = z - 1
Loop``````
The code works well except that the MINIFS formulas get changed to F4:X9, F5:X10, F6:X11, etc. instead of F4:X4, F5:X5, F6:X6, etc.

Need help to accomplish this, not necessarily with using MINIFS or MAXIFS. Thanks

#### jasonb75

##### Well-known Member
You latest proposed AGGREGATE formula did not work. For some reason Excel does not seem to like R7C7:R7C25,it wants to change it to RC77:RC725
If you look, my reply contained vba code not formulas, it is to use in place of your original copy and paste code.
The formulas are written in R1C1 notation, so if you try to enter them directly into excel then it will not work (unless you change settings so that R1C1 is accepted, but I wouldn't advise doing that).

Using R1C1 in formulas, R7C7 means row 7, column 7 (i.e. G7) as an absolute reference. When there is no number between R and C, i.e. RC6:RC24, it tells the formula to look at the row that it is in, so in D4, RC6 translates to F4, in D5, RC6 translates to F5, etc. Again, this only works if you apply the formula through vba, typing RC6 into a cell will treat RC as a regular column, not a row and column intersection.

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### JARichard74

##### Board Regular
I get a compile error, Syntax error. Have not been able to figure out what the error is

#### jasonb75

##### Well-known Member
Oops, that was my bad, I forgot to double up the double quotes in the formulas, it should have been
VBA Code:
``````Range("D4:D8").FormulaR1C1 = "=IFERROR(AGGREGATE(15,6,RC6:RC24/(RC6:RC24<>"""")/(R7C7:R7C25=""COMPLIANT""),1),""No Data"")"
Range("E4:E8").FormulaR1C1 = "=IFERROR(AGGREGATE(14,6,RC6:RC24/(RC6:RC24<>"""")/(R7C7:R7C25=""COMPLIANT""),1),""No Data"")"``````
I've also added an error trap to cover the #NUM! errors in the sheet when there is no data meeting the criteria, i.e all value cells are empty and/or none of the cells in row 7 = 'COMPLIANCE'

The code is based on your original formula where 'COMPLIANCE' was specified in row 7, however I note that in your data samples, that actually appears in row 11. It is not entirely clear which is correct. If it should be row 11 then you need to change R7C to R11C in the code (4 places).

#### JARichard74

##### Board Regular
Wow, early riser! Yes, the COMPLIANCE row starts at row 7 and then moves based on the number of rows I add to the table. The D4:D8 and E4:E8 is fine for testing however I will eventually have to figure out to fill out the table from D4 and E4 to the last row. I probably have a coding error in my script to create the Table. If I use you code exactly then my script runs and I have the columns with No Data which makes sense as there is no data. However, as I type data in the first cell I get a circular reference error and the formulas do not work. If I remove the IFERROR check from your formula then my script blocks on that line of code. When I remove your code, the script runs correctly.
VBA Code:
``````Sub SetupCalc()
Const MyPassword As String = "dcccdc"
Dim ChangeProtection As Boolean
Dim z As Integer
Dim tbl As ListObject
Dim tblRow As ListRow
Set tbl = ThisWorkbook.Worksheets("Calc").ListObjects("Results")

' Removes the Protection
With Sheets("Calc")
If .ProtectContents = True Then
ChangeProtection = True
End If
' Check that the Bidders and Items tables are created in sheet Prep
If WorksheetFunction.CountA(ThisWorkbook.Worksheets("Prep").Range("G1")) = 0 Then
Exit Sub
End If
If WorksheetFunction.CountA(ThisWorkbook.Worksheets("Prep").Range("G3")) = 0 Then
Exit Sub
End If
' Copies the names of the Bidders from the Bidders Table in sheet Prep

ThisWorkbook.Worksheets("Calc").Range("F2").Value = ThisWorkbook.Worksheets("Prep").Range("B9").Value
ThisWorkbook.Worksheets("Calc").Range("H2").Value = ThisWorkbook.Worksheets("Prep").Range("B10").Value
ThisWorkbook.Worksheets("Calc").Range("J2").Value = ThisWorkbook.Worksheets("Prep").Range("B11").Value
ThisWorkbook.Worksheets("Calc").Range("L2").Value = ThisWorkbook.Worksheets("Prep").Range("B12").Value
ThisWorkbook.Worksheets("Calc").Range("N2").Value = ThisWorkbook.Worksheets("Prep").Range("B13").Value
ThisWorkbook.Worksheets("Calc").Range("P2").Value = ThisWorkbook.Worksheets("Prep").Range("B14").Value
ThisWorkbook.Worksheets("Calc").Range("R2").Value = ThisWorkbook.Worksheets("Prep").Range("B15").Value
ThisWorkbook.Worksheets("Calc").Range("T2").Value = ThisWorkbook.Worksheets("Prep").Range("B16").Value
ThisWorkbook.Worksheets("Calc").Range("V2").Value = ThisWorkbook.Worksheets("Prep").Range("B17").Value
ThisWorkbook.Worksheets("Calc").Range("X2").Value = ThisWorkbook.Worksheets("Prep").Range("B18").Value

z = ThisWorkbook.Worksheets("Prep").Range("G4").Value
' Add the required number of rows to the Results table in sheet Calc
Do Until z = 0
tblRow.Range.Offset(-1).Copy
tblRow.Range.PasteSpecial xlPasteFormulasAndNumberFormats
z = z - 1
Loop

' Hides the columns that are not required
Dim C1 As Range
For Each C1 In ThisWorkbook.Worksheets("Calc").Range("F1:Y1")
C1.EntireColumn.Hidden = C1.Value = "0"
Next C1

' Copies the formulas for SMALL and LARGE
' Range("D4:D8").FormulaR1C1 = "=IFERROR(AGGREGATE(15,6,RC6:RC24/(RC6:RC24<>"""")/(R7C7:R7C25=""COMPLIANT""),1),""No Data"")"
' Range("E4:E8").FormulaR1C1 = "=IFERROR(AGGREGATE(14,6,RC6:RC24/(RC6:RC24<>"""")/(R7C7:R7C25=""COMPLIANT""),1),""No Data"")"

' Protects the sheet
If ChangeProtection = True Then .Protect (MyPassword)
End With
Application.CutCopyMode = False
Worksheets("Calc").Activate
Range("F4").Select
End Sub``````

#### jasonb75

##### Well-known Member

Going back to the sample in post 10, exactly what range does the 'Results' table cover before you run the code?
If it's not full of confidential data, could you perhaps upload a copy of the workbook to dropbox or similar, where there is data ready to be processed, so that the next thing to do is run the code.

#### JARichard74

##### Board Regular
\$A\$3:\$Y\$4 with ROW 3 being the header row

#### jasonb75

##### Well-known Member

Did you catch my edit above?
There is something (probably simple) that I'm not seeing, it would help to have the actual file to run a test on if that is possible.
Ideally with a clean 'Calc' sheet and data in the 'Prep' sheet that is ready to be copied over.

#### JARichard74

##### Board Regular
OK however I do not believe I can upload an Excel file to the BB

#### jasonb75

##### Well-known Member
No, you would need to use service like dropbox and post the link here.

Replies
2
Views
314
Replies
4
Views
125
Replies
3
Views
52
Replies
0
Views
214
Replies
8
Views
91

1,127,491
Messages
5,625,075
Members
416,068
Latest member
seba_s

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back