# 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

#### JARichard74

##### Board Regular
Got it working using this. Runs perfectly from the VBA module but I get an error when I use the button in sheet Calc
VBA Code:
``````With tbl.Range
lr = .Rows(.Rows.Count).Row + 3
Range("D4:D7").FormulaR1C1 = "=MINIFS(RC6:RC24,R" & lr & "C7:R" & lr & "C25,""COMPLIANT"")"
Range("E4:E7").FormulaR1C1 = "=MAXIFS(RC6:RC24,R" & lr & "C7:R" & lr & "C25,""COMPLIANT"")"
End With``````

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### jasonb75

##### Well-known Member
Been offline for a while, glad to see you figured it out

You just need to add the sheet name in front of the ranges for it to work on the button, without a sheet name, Range is always applied to the selected sheet.
As it's a table, you should be able to refer to it directly, I'm not entirely sure of the correct syntax for it but will take a look later for you.

#### JARichard74

##### Board Regular
Yes, I figured that was the issue. Thanks very much for your help. I am very new to VBA and this was very helpful. Take care!

Replies
2
Views
308
Replies
4
Views
123
Replies
2
Views
42
Replies
0
Views
211
Replies
8
Views
91

1,126,945
Messages
5,621,760
Members
415,854
Latest member
Tutu123

### 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