# Referencing

This is a discussion on Referencing within the Excel Questions forums, part of the Question Forums category; I am using the following formula: =SUMPRODUCT((Detail!\$A\$3:\$A\$106=\$A\$37)*(Detail!\$D\$3:\$D\$106=A38),Detail!AD\$3:AD\$106) I want to enter the column to sum in a cell and have ...

1. ## Referencing

I am using the following formula:

I want to enter the column to sum in a cell and have the formula reference that cell. For example, if in cell A1 I type BB then the formula would in effect be:

=SUMPRODUCT((Detail!\$A\$3:\$A\$106=\$A\$37)*(Detail!\$D\$3:\$D\$106=A38),Detail!BB\$3:BB\$106)

If in A1 I type BC then the formula would reference BC as the column to sum:

=SUMPRODUCT((Detail!\$A\$3:\$A\$106=\$A\$37)*(Detail!\$D\$3:\$D\$106=A38),Detail!BC\$3:BC\$106)

Thanks

2. ## Re: Referencing

Originally Posted by HondaTiger
I am using the following formula:

I want to enter the column to sum in a cell and have the formula reference that cell. For example, if in cell A1 I type BB then the formula would in effect be:

=SUMPRODUCT((Detail!\$A\$3:\$A\$106=\$A\$37)*(Detail!\$D\$3:\$D\$106=A38),Detail!BB\$3:BB\$106)

If in A1 I type BC then the formula would reference BC as the column to sum:

=SUMPRODUCT((Detail!\$A\$3:\$A\$106=\$A\$37)*(Detail!\$D\$3:\$D\$106=A38),Detail!BC\$3:BC\$106)

Thanks
=SUMPRODUCT((Detail!\$A\$3:\$A\$106=\$A\$37)*(Detail!\$D\$3:\$D\$106=A38),INDIRECT("Detail!"&A1&"3:"&A1&"106"))

3. ## great, thanks

works (had to add 1 &) but the solution works, thanks a bunch...

I'm quickly becoming a real regular on this board. I'll be glad when I can start to return the favors.

4. ## Re: Referencing

Originally Posted by HondaTiger
I am using the following formula:

I want to enter the column to sum in a cell and have the formula reference that cell. For example, if in cell A1 I type BB then the formula would in effect be:

=SUMPRODUCT((Detail!\$A\$3:\$A\$106=\$A\$37)*(Detail!\$D\$3:\$D\$106=A38),Detail!BB\$3:BB\$106)

If in A1 I type BC then the formula would reference BC as the column to sum:

=SUMPRODUCT((Detail!\$A\$3:\$A\$106=\$A\$37)*(Detail!\$D\$3:\$D\$106=A38),Detail!BC\$3:BC\$106)

Thanks
What is the range (AD to BC?) from which you choose a columnar range to sum? And do the ranges to sum have a label in row 2?

5. ## Re: great, thanks

Originally Posted by HondaTiger
works (had to add 1 &) but the solution works, thanks a bunch...

I'm quickly becoming a real regular on this board. I'll be glad when I can start to return the favors.
Sorry, about the missing ampersand... I was editing your original formula in a rush to leave for the day and missed one. Glad to hear that it did the trick.

6. Maybe I should have motivated my question. My intent was to reserach the possibility of avoiding a volatile function like INDIRECT and OFFSET in your SumProduct formula. Such functions invariably degrades the performance...

The setup I had in mind is to use nonvolatile INDEX instead, as the demo below shows...

******** ******************** ************************************************************************>
 Microsoft Excel - Book2 ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 D13E13D14E14 =

A
B
C
D
E
F
G
H
1
********
2
F1F2*WxXxYyZz*
3
A1*1715977200*
4
B3*737715224*
5
A1*1884178188*
6
C3*13413014577*
7
A3*10726140145*
8
B1*1231790121*
9
C2*52183103148*
10
********
11
********
12
********
13
A1Xx2200***
14
B3Yy3152***
15
********
 Demo *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The formulas are...

D13:

=MATCH(C13,\$D\$2:\$G\$2,0)

E13:

=SUMPRODUCT((\$A\$3:\$A\$9=\$A13)*(\$B\$3:\$B\$9=\$B13),INDEX(\$D\$3:\$G\$9,0,\$D13))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•