Reference Cell in Formula

[chrysti]

I was needing to refer to a dropdown box to change my sum range in my formula

=SUMPRODUCT(--('LINE GUIDE'!$C$2:$C$65536=$A51),--('LINE GUIDE'!$F$2:$F$65536="In-Line"),--('LINE GUIDE'!$I$2:$I$65536=$B51),('LINE GUIDE'!$BE$2:$BE$65536))

this is my formula...but only the sum range needs to changed based on by dropdown criteria:

Dropbox says 1 I want my formula to say =SUMPRODUCT(--('LINE GUIDE'!$C$2:$C$65536=$A51),--('LINE GUIDE'!$F$2:$F$65536="In-Line"),--('LINE GUIDE'!$I$2:$I$65536=$B51),('LINE GUIDE'!$BE$2:$BE$65536))

Dropbox says 2 I want my formula to say =SUMPRODUCT(--('LINE GUIDE'!$C$2:$C$65536=$A51),--('LINE GUIDE'!$F$2:$F$65536="In-Line"),--('LINE GUIDE'!$I$2:$I$65536=$B51),('LINE GUIDE'!$BF$2:$BF$65536))

Dropbox says 3 I want my formula to say =SUMPRODUCT(--('LINE GUIDE'!$C$2:$C$65536=$A51),--('LINE GUIDE'!$F$2:$F$65536="In-Line"),--('LINE GUIDE'!$I$2:$I$65536=$B51),('LINE GUIDE'!$BG$2:$BG$65536))

etc I have 11 different variables so an IF won't work.

Please help! Thanks in advance

 

[AlphaFrog]

Replace this...
'LINE GUIDE'!$BE$2:$BE$65536

With this...
INDEX('LINE GUIDE'!$BE$2:$BG$65536, Dropbox_Value)

This will select the column where 1 = column BE, 2 = column BF... etc.

Also note: SUMPRODUCT will make a calculation for every cell you reference e.g. 2 to 65536 even if they are empty. This could be very slow to reference such a large range. It's recommended to use only as large a range as needed for efficiency. You may want to create some Dynamic Named Ranges and use them in the formula.

 

[chrysti]

I need to use this for 4 different columns under 11 different headers and my data goes down from 1000 to 30000 rows it varies based on when the data is pulled...is there a better formula??? Please enlighten as speed of calculations is always a top priority.

Thanks in advance

 

[T. Valko]

I need to use this for 4 different columns under 11 different headers and my data goes down from 1000 to 30000 rows it varies based on when the data is pulled...is there a better formula??? Please enlighten as speed of calculations is always a top priority.

Thanks in advance

If you're using Excel 2007 or later you can use the SUMIFS function which is more efficient than SUMPRODUCT.

 

[chrysti]

I tried this and it didn't work:
=SUMPRODUCT(--('LINE GUIDE'!$C$2:$C$65536=$A51),--('LINE GUIDE'!$F$2:$F$65536="In-Line"),--('LINE GUIDE'!$I$2:$I$65536=$B51),(INDEX('LINE GUIDE'!$BE$1:$DA$65536,C$50)))

Thanks in advance

 

[AlphaFrog]

Sorry, it should have been...

=SUMPRODUCT(--('LINE GUIDE'!$C$2:$C$65536=$A51),--('LINE GUIDE'!$F$2:$F$65536="In-Line"),--('LINE GUIDE'!$I$2:$I$65536=$B51),(INDEX('LINE GUIDE'!$BE$1:$DA$65536,,C$50)))

 

[T. Valko]

Replace this...
'LINE GUIDE'!$BE$2:$BE$65536

With this...
INDEX('LINE GUIDE'!$BE$2:$BG$65536, Dropbox_Value)

This will select the column where 1 = column BE, 2 = column BF... etc.

Also note: SUMPRODUCT will make a calculation for every cell you reference e.g. 2 to 65536 even if they are empty. This could be very slow to reference such a large range. It's recommended to use only as large a range as needed for efficiency. You may want to create some Dynamic Named Ranges and use them in the formula.

You need to include both a ROW and COLUMN argument.

INDEX('LINE GUIDE'!$BE$2:$BG$65536,0,Dropbox_Value)

 

[chrysti]

This doesn't sum my range it finds the column I want to sum but just gives back an error.

Please help!

 

[AlphaFrog]

This doesn't sum my range it finds the column I want to sum but just gives back an error.

How do you know it finds the column?

What exactly is in the dropdown list? Are there numbers 1,2,3 or are there text that look like numbers "1", "2", "3"

If you have any error values in the selected column or in columns C, F, or I, then SUMPRODUCT will return an error.

What error do you get?

 

[T. Valko]

Sorry, it should have been...

=SUMPRODUCT(--('LINE GUIDE'!$C$2:$C$65536=$A51),--('LINE GUIDE'!$F$2:$F$65536="In-Line"),--('LINE GUIDE'!$I$2:$I$65536=$B51),(INDEX('LINE GUIDE'!$BE$1:$DA$65536,,C$50)))

All of the ranges are not the same size.

Maybe that should be: $BE$2