Name Range in SumIfs (to become dynamic)

[countryfan_nt]

Hello friends! hope all is well,

I created a name range called Col, it counts the number of rows.

I am trying to replace the number 1000 with COL. Can that be done? I tried severa; ways, but no luck.
=SUMIFS(A!F2:F1000,A!C2:C1000,F9,A!D2:D1000,"Rev",A!E2:E1000,B14,A!G2:G1000,Sheet2!F11,A!H2:H1000,Sheet2!F12)

Can you please help and thank you very much in advance!

 

[Joe4]

If it is truly a named range, just use the named range and the OFFSET function in your formula.

For example, let say I data where I have a value I am checking for in column A, a value I am checking for in column B, and I want to sum the amounts from column C.
Let's say that I have a named range named "MyRange", and it is the column A values. Here is what the formula would look like:

=SUMIFS(OFFSET(MyRange,0,2),MyRange,"a",OFFSET(MyRange,0,1),"b")

 

[countryfan_nt]

thank you! but still stuck. Can you please help me adjust the formula to look something like the below?

=SUMIFS(A!F2:F&myrange,A!C2:C&myrange,F9,A!D2:D&myrange …etc

 

[Joe4]

What is myrange in your formula?
If it is a named range, what range is it?
If it is just one cell (your ending cell), what not make it the whole range instead of one cell (i.e. starting at F2 ending at your ending cell)?

 

[countryfan_nt]

Thanks! OK, I solved it with this formula: =IF($F$6="FY",SUMIFS(FC,CC,$F$4,DC,"Rev",EC,B7,GC,VS!$F$5),SUMIFS(FC,CC,$F$4,DC,"Rev",EC,B7,GC,VS!$F$5,HC,VS!$F$6))

I was hoping to do thru this formula =SUMIFS(A!F2:F&myrange,A!C2:C&myrange,F9,A!D2:D&myrange …etc

but hey, as long as something works! thanks a lot

 

[Aladin Akyurek]

Hello friends! hope all is well,

I created a name range called Col, it counts the number of rows.

I am trying to replace the number 1000 with COL. Can that be done? I tried severa; ways, but no luck.
=SUMIFS(A!F2:F1000,A!C2:C1000,F9,A!D2:D1000,"Rev",A!E2:E1000,B14,A!G2:G1000,Sheet2!F11,A!H2:H1000,Sheet2!F12)

Can you please help and thank you very much in advance!

Either...

=SUMIFS(A!F2:INDEX(F:F,Col),A!C2:INDEX(C:C,col),F9,A!D2:INDEX(D:D,Col),"Rev",A!E2:INDEX(E:E,Col),B14,A!G2:INDEX(G:G,Col),F11,A!H2:INDEX(H:H,Col),F12)

Or just...

=SUMIFS(A!F:F,A!C:C,F9,A!D:D,"Rev",A!E:E,B14,A!G:G,F11,A!H:H,F12)
​