Excel // Dynamic Range in Sum If Function

Question

I’m trying to have a quite complicated Sum if function more reliable. The situation is that I’m using that formula (see below) to sum values between two dates from a separate sheet in the workbook.

=SUMIFS(wochen!$F11:$BM11;wochen!$F$8:$BM$8;">="&DATE(YEAR(T$8);1;1);wochen!$F$8:$BM$8;"<="&DATE(YEAR(T$8);MONTH(T$8);DAY(T$8)))

The handicap is that I was hoping to implement some Kind of Lookup function or match function to always get the values from the correct row. So far the sum_range is static and I can’t make sure its picking the correct line.

I already played around with this kind Address / Match Function =ADDRESS(MATCH($B13;'F_P&L'!$B$1:$B$267;0);MATCH(T$6;'F_P&L'!$F$6:$CP$6;0);;;"wochen!") into that Sum_Range of the Sum If Formular but that doesn’t work either as it comes out as Text

I’m happy for any Idea which van be implemented via excel Formulas or VBA

I hope I explained everything clear, if not I'm happy to provide more information's

Many Thanks in Advance

Dennis

Answer 1

Here is a simple example which you can extend to your case. It uses the =INDIRECT() formula.

If you know through some way what the correct rows are, say you know your range of interest is in line 11, ...

Put the following in a cell

A1:    = 11                   ' You can use VLOOKUP etc. to generate the row number
B1:    = "F" & A1 & ":BM"&A1  ' That makes B1 read: F11:BM11
C1:    = SUM(INDIRECT(B1))    ' This will make it the equivalent of writing =SUM(F11:BM11)

Writing =function(INDIRECT(*anything*)) where *anything* is a string describing the address of a range is the equivalent of writing =function(** selecting the range **)

Note on Performance: Do note, however, that INDIRECT is a volatile functions so any changes in the workbook with calculations set to automatic will cause it to recalculate. So there might be performance implications in certain cases.