Count With Address

[cadence]

a quicky

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

EG A1=5 A3=10

=COUNTA(C2ADDRESS(A3+1,A1+2)))

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

thanks

 

[dafan]

This is where INDIRECT comes in again Magic formula :D

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

 

[cadence]

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]

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?

 

[cadence]

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.

Cascading downlist test3.xls
	A	B	C	D	E	F	G	H	I
1	5	Area 1	Area 1	Area 2	Area 3	Area 4	Area 5
2		Area 2	Item 1	item 15	Itme 34	Item 46	Item 63
3	8	Area 3	Item 2	item 16	Itme 35	Item 47	Item 64
4		Area 4	Item 3	item 17	Itme 36	Item 48	Item 65
5	$G$2:$G$100	Area 5	Item 4	item 18	Itme 37	Item 49	Item 66
6			Item 5	item 19	Itme 38	Item 50	Item 67
7	$G$9		Item 6	item 20	Itme 39	Item 51	Item 68
8			Item 7	item 21	Itme 40	Item 52	Item 69
9	40		Item 8	item 22	Itme 41	Item 53	Item 70
10			Item 9	item 23	Itme 42	Item 54	Item 71
11			Item 10	item 24	Itme 43	Item 55	Item 72
12			Item 11	item 25	Itme 44	Item 56	Item 73
13			Item 12	item 26	Itme 45	Item 57	Item 74
14			Item 13	item 27		Item 58	Item 75
15			Item 14	item 28		Item 59	Item 76
16				item 29		Item 60	Item 77
17				item 30		Item 61	Item 78
18				item 31		Item 62	Item 79
19				item 32			Item 80
20				item 33			Item 81
21							Item 82
22							Item 83
23							Item 84
Budget

 

[cornflakegirl]

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

 

[cornflakegirl]

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,))

 

[cadence]

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]

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 .

 

[cadence]

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.