##### Well-known Member
a quicky

how can I re-write this expression to count from C2 to the address

EG A1=5 A3=10

i'm trying to count all cell with items in range C2:G25 up to where ever the address data goes to?

thanks

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### dafan

##### Well-known Member
This is where INDIRECT comes in again Magic formula :D

Code:
``=COUNTA(C2:INDIRECT(ADDRESS(A3+1,A1+2)))``

##### Well-known Member
Your a legend, I'm going to have to look it up [indirect] to see what it does,

but I realised that I need to tweak so that it counts the entire column up to the address, else I clip information or certain calculations

any ideas how to do that?

thanks

#### dafan

##### Well-known Member
Could you show us how your current sheet looks like (or just a bit) and what your desired output is?

As far as I'm concerned you should now just change C2 to C1?

##### Well-known Member
Sorry, caught called away and this is my first html post!!!

so there are two drop list that [not visible] A1 select the Area [column C-G], so that A5 is the column range to give the second drop list a choice. then A3 which row in the at column. The two create an address in A7. Whilst A9=COUNTA(C2:INDIRECT(ADDRESS(A3+1,A1+2))).

The trouble now I having is that if the address is say G9, the count ignores value below C9,D9,E9 & F9 because the range is C2:G9, not all of column C,D,E,F to G9.

Hope that make sense and haven't scared you off. thanks for your assistance.
ABCDEFGHI
15Area 1Area 1Area 2Area 3Area 4Area 5
2Area 2Item 1item 15Itme 34Item 46Item 63
38Area 3Item 2item 16Itme 35Item 47Item 64
4Area 4Item 3item 17Itme 36Item 48Item 65
5\$G\$2:\$G\$100Area 5Item 4item 18Itme 37Item 49Item 66
6Item 5item 19Itme 38Item 50Item 67
7\$G\$9Item 6item 20Itme 39Item 51Item 68
8Item 7item 21Itme 40Item 52Item 69
940Item 8item 22Itme 41Item 53Item 70
10Item 9item 23Itme 42Item 54Item 71
11Item 10item 24Itme 43Item 55Item 72
12Item 11item 25Itme 44Item 56Item 73
13Item 12item 26Itme 45Item 57Item 74
14Item 13item 27Item 58Item 75
15Item 14item 28Item 59Item 76
16item 29Item 60Item 77
17item 30Item 61Item 78
18item 31Item 62Item 79
19item 32Item 80
20item 33Item 81
21Item 82
22Item 83
23Item 84
Budget

#### cornflakegirl

##### Well-known Member
cadence - do the five lists need to be in 5 different columns? Are they like that because you may want to add items?

#### cornflakegirl

##### Well-known Member
I think this formula works, but the OFFSETs make it volatile, so there may be a more efficient way:

=COUNTA(OFFSET(\$C:\$C,0,0,,\$A\$1-1))-\$A\$1+(\$A\$1>1)+COUNTA(OFFSET(\$C\$2,0,\$A\$1-1,\$A\$3,))

##### Well-known Member
That is awesome, I still trying to understand it but works in all cases

In answer to your first question, Ideally No, I had put them that way because I didn't know how to make a cascading DropList take reference from different parts of the same column, The items will be fixed.

If I did know it I think it would eliminate the need for your super formula.

If you can cascade from one column source I'b be curious, but very appreciative of the formula. [any chance of a 25 words or less explanation?]

what makes it volatile?

Are you by chance a tory amos fan?

cheers

#### cornflakegirl

##### Well-known Member
1. If you have fixed lists, I would use named ranges. For example, the list currently in column G could be named Range5 - then the validation range for the second dropdown could be set as =INDIRECT("Range"&\$A\$1).

If I've completely missed the point of why you used 5 columns, then shout. (I assume that, since you already appear to have a dependent dropdown, you're already sussed on how these work.)

2. The formula is in two parts. =COUNTA(OFFSET(\$C:\$C,0,0,,\$A\$1-1))-\$A\$1+(\$A\$1>1) starts with column C, moves it 0 rows down and 0 columns right, doesn't change its row dimensions, but expands it to \$A\$1-1 columns (check the help on OFFSET for more details). Then it counts the number of entries in these columns. It does this because, if the cell of interest is in the fifth column, we want all the entries in the previous four columns. We then take off (\$A\$1 - 1) to discount the header items. Then we deal with the case where A1 = 1 (so we want this whole first section to return 0). COUNTA(OFFSET(\$C:\$C,0,0,,0)) = 1, so if A1 = 1, we need to -1, but not otherwise - so we can use ((\$A\$1>1)-1). And -(\$A\$1 - 1) + ((\$A\$1>1)-1) = -\$A\$1 + (\$A\$1>1). So that's the whole first bit.

+COUNTA(OFFSET(\$C\$2,0,\$A\$1-1,\$A\$3,)) just counts the number of items in the A1th column, as far down as the A3th row - pretty similar to the formula you already had.

Apologies for more than 25 words - it's tricky without knowing which bits you already understand!

3. Offset is defined as a volatile formula. This means that every time something in your sheet changes, offset will recalculate, which can make your spreadsheet slow if you have a lot of formulas doing this.

4. My husband is the fan. The first line sounds a bit like "Emma was a cornflake girl" - if you listen really hard .

##### Well-known Member
1. I'm going to investigate this as it could be a good improvement. say that was true would it effect the your calculation greatly?

2. Wow, wow wow, the 25 words was just to help minimise the help of a plebe on the other side of the world. having said that. that is incredibly insightful [after reading it 6 times] and not in a month [nah year] of sundays would I have got that especially the last part.

3. good to note, its the only one, and its going to be a hidden sheet.

4. cool, a lot of her songs have have that double sound to them.

cheers. very grateful.

Replies
1
Views
74
Replies
9
Views
490
Replies
1
Views
84
Replies
4
Views
217
Replies
4
Views
155

1,191,524
Messages
5,987,103
Members
440,079
Latest member
MarchePR

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back