using labels as reference to count

  • Thread starter Thread starter Roy Shine
  • Start date Start date
R

Roy Shine

I'm trying to figure out what function or combination of
functions would allow me to search for a label in a
column, then count the items in the cells in the ajacent
rows.

The sticking point is that the labels are sorted, so my
cell reference would change on a irregular interval which
will not allow me to set up a specific range to count
from. Any ideas would be appreciated.

I believe I can use a function to count and read the
labels, but the variable label location is what is
throwing me for a loop right now.

Again any help would be appreciated.

Roy
 
Not sure exactly what you want.

If you have set up your labels using Insert/Name/Create, each column
label becomes a named range, so you can use formulas like :-

'-----------------------------------------------
=SUM(Field1)
=COUNT(Field1)
=SUMIF(Field2,"test",Field1) ....
(sums Field1 where Field2 ="test")
'-----------------------------------------------
Or, to find the column number of a field name :-
=MATCH("Field1",A1:G1,0)
'-----------------------------------------------
Name A1:G1 as "Headings" to use :-
=MATCH("Field1",Headings,0)
'-----------------------------------------------
to use in a formula like (should be 1 line):-
=SUM(INDIRECT("R2C" &MATCH("Field1",Headings,0)
&":R6C"&MATCH("Field1",Headings,0),FALSE))
'-----------------------------------------------

Look up Label ... Ranges (etc) in help to see how Row/Column names can
be used in combination. (don't get confused with data labels in
charts).

To get further, help post a new message being more specific about how
your data is arranged, what you have done, and what you are trying to
achieve. Many of us tend not to read messages already answered.

If it gets more complicated than this I tend to prefer a VBA custom
function.

Regards
BrianB
==============================================================
 
Back
Top