| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count in a Cell Reference
|
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count all but a specific reference cell (countif ?) + | Excel Worksheet Functions | |||
| Count # of times a Reference is used | Excel Discussion (Misc queries) | |||
| How can I reference a cell as the criteria in a count if function. | Excel Discussion (Misc queries) | |||
| problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
| Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions | |||
Hybrid Mode
