MINIFS WITH OFFSET FORMULA

Question

Hi I am trying to use the MINIFS function with OFFSET so that when I enter new rows in "IM TARIFF" sheet it takes them into consideration. But I get #VALUE! error. Here is the formula:

=MINIFS(OFFSET('IM Tariff'!$K$1,0,0,COUNT('IM Tariff'!K:K),1),OFFSET('IM Tariff'!$C$1,0,0,COUNT('IM Tariff'!C:C),1),G3,OFFSET('IM Tariff'!$F$1,0,0,COUNT('IM Tariff'!F:F),1),J3)

Could someone help me?

Answer 1

Hi,

You do not need to OFFSET() function at all.  Select the entire range of data (including the header row) and press Ctrl+T to convert it into a Table.  Now simplify your formula to

=MINIFS('IM Tariff'!$K$2:$K$100,'IM Tariff'!$C$2:$C$100,G3,'IM Tariff'!$F$2:$F$100,J3)

I have assumed that you have data only till row 100.  Now as and when you add data from row 101, the range in your formula will auto extend.

Hope this helps.

Answer 2

You can use entire columns with that function:

=MINIFS('IM Tariff'!K:K,'IM Tariff'!C:C,G3,'IM Tariff'!F:F,J3)

If you have headers or other strings in those columns, you would need to use COUNTA instead of COUNT (COUNTA counts ALL, COUNT counts only numbers) - so your error was probably due to the difference returned by the various COUNT functions - any of the XXXIF functions require same-sized arrays of values to be passed - as long as you have no gaps in your columns, this should work:

=MINIFS(OFFSET('IM Tariff'!$K$1,0,0,COUNTA('IM Tariff'!K:K),1),OFFSET('IM Tariff'!$C$1,0,0,COUNTA('IM Tariff'!C:C),1),G3,OFFSET('IM Tariff'!$F$1,0,0,COUNTA('IM Tariff'!F:F),1),J3)

If you have gaps in one or more columns, then tie your COUNTA to one column, the same for all three (K:K in this example):

=MINIFS(OFFSET('IM Tariff'!$K$1,0,0,COUNTA('IM Tariff'!K:K),1),OFFSET('IM Tariff'!$C$1,0,0,COUNTA('IM Tariff'!K:K),1),G3,OFFSET('IM Tariff'!$F$1,0,0,COUNTA('IM Tariff'!K:K),1),J3)

Of course, none of that is required - just wanted to explain why you were getting the error.