If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count in a Cell Reference
I have a count formula in cell A1 that = 25
I would like to be refer to that number in another cell reference. For example SUM(A1:A?) where the question mark = the value in A1. This is probably easy but I just coundn't figure it out on my own. |
#2
|
|||
|
|||
Count in a Cell Reference
Try
=SUM(INDIRECT("A1:A" & A1)) If this post helps click Yes --------------- Jacob Skaria "Johnny" wrote: I have a count formula in cell A1 that = 25 I would like to be refer to that number in another cell reference. For example SUM(A1:A?) where the question mark = the value in A1. This is probably easy but I just coundn't figure it out on my own. |
#3
|
|||
|
|||
Count in a Cell Reference
Try this...
C1 = 25 =SUM(A1:INDEX(A:A,C1)) If C1 is an empty cell then the formula will calculate the entire range A:A. -- Biff Microsoft Excel MVP "Johnny" wrote in message ... I have a count formula in cell A1 that = 25 I would like to be refer to that number in another cell reference. For example SUM(A1:A?) where the question mark = the value in A1. This is probably easy but I just coundn't figure it out on my own. |
#4
|
|||
|
|||
Count in a Cell Reference
Yes that helps. Could you help figure out how to adapt it into the following
formula? SUM(INDEX(('All D-Load'!$A$1:$A$4093=$A11) Where 4093 is the variable based on the count in cell A1 from below. "Jacob Skaria" wrote: Try =SUM(INDIRECT("A1:A" & A1)) If this post helps click Yes --------------- Jacob Skaria "Johnny" wrote: I have a count formula in cell A1 that = 25 I would like to be refer to that number in another cell reference. For example SUM(A1:A?) where the question mark = the value in A1. This is probably easy but I just coundn't figure it out on my own. |
#5
|
|||
|
|||
Count in a Cell Reference
Not sure what tha formula does. since that is not complete
Anway replace 'All D-Load'!$A$1:$A$4093 with INDIRECT("'All D-Load'!$A1:A" & A1) If this post helps click Yes --------------- Jacob Skaria "Johnny" wrote: Yes that helps. Could you help figure out how to adapt it into the following formula? SUM(INDEX(('All D-Load'!$A$1:$A$4093=$A11) Where 4093 is the variable based on the count in cell A1 from below. "Jacob Skaria" wrote: Try =SUM(INDIRECT("A1:A" & A1)) If this post helps click Yes --------------- Jacob Skaria "Johnny" wrote: I have a count formula in cell A1 that = 25 I would like to be refer to that number in another cell reference. For example SUM(A1:A?) where the question mark = the value in A1. This is probably easy but I just coundn't figure it out on my own. |
#6
|
|||
|
|||
Count in a Cell Reference
I incorporated your recommendation into my formula and it worked for the most
part. There were two conditions in which I couldn't get it to work though. Here's the formula: =SUM(IF(FREQUENCY(IF(INDIRECT("'All D-Load'!$A$2:$A" & $B$21) =$A11,IF(INDIRECT("'All D-Load'!$C$2:$C" & $B$21) "T",IF(INDIRECT("'All D-Load'!$D$2:$D" & $B$21) =B$1,IF(INDIRECT("'All D-Load'!$D$2:$D" & $B$21) =B$2,MATCH('All D-Load'!$B$2:$B$4093,'All D-Load'!$B$2:$B$4093,0))))),ROW(INDIRECT("'All D-Load'!$B$2:$B" & $B$21))-MIN(ROW(INDIRECT("'All D-Load'!$B$2:$B" & $B$21)))+1),1)) The piece I can't seem to get it to work is: MATCH('All D-Load'!$B$2:$B$4093,'All D-Load'!$B$2:$B$4093,0) where $B$4093 is what I need to replace with cell reference $B$21 like I did with the others. Thank you "Jacob Skaria" wrote: Not sure what tha formula does. since that is not complete Anway replace 'All D-Load'!$A$1:$A$4093 with INDIRECT("'All D-Load'!$A1:A" & A1) If this post helps click Yes --------------- Jacob Skaria "Johnny" wrote: Yes that helps. Could you help figure out how to adapt it into the following formula? SUM(INDEX(('All D-Load'!$A$1:$A$4093=$A11) Where 4093 is the variable based on the count in cell A1 from below. "Jacob Skaria" wrote: Try =SUM(INDIRECT("A1:A" & A1)) If this post helps click Yes --------------- Jacob Skaria "Johnny" wrote: I have a count formula in cell A1 that = 25 I would like to be refer to that number in another cell reference. For example SUM(A1:A?) where the question mark = the value in A1. This is probably easy but I just coundn't figure it out on my own. |
#7
|
|||
|
|||
Count in a Cell Reference
|
Thread Tools | |
Display Modes | |
|
|