Incrementing a value in a function

[Stefan Keydel]

Greetings,

Is it possible to automatically increment a value in a function? For
example (and this is silly one, but...), let's say I'm using SUMPRODUCT
to count the number of occurances of specific bowling scores in Austin,
TX bowling alleys:

=SUMPRODUCT((City="AUSTIN")*(Score="275"))

Now, let's say I want to do this for every possible score between 0 and
300. What I would typically do is enter the formula in one cell and then
click-drag to the right for 300 cells; I'd then have to edit each cell
to include the appropriate value.

How would I go about incrementing the score without having to click on
each cell and manually edit the number? Or, given what I'm trying to do,
is there a completely different way of going about this?


Thanks,

Stefan

 

[Arvi Laanemets]

Hi

Into p.e. A2 enter the formula
=SUMPRODUCT((City="AUSTIN")*(Score="" & COLUMN()))
The same with starting formula in p.e. C2
=SUMPRODUCT((City="AUSTIN")*(Score="" & (COLUMN()+2)))

Or when formulas are in column, starting from A2
A2=SUMPRODUCT((City="AUSTIN")*(Score="" & (ROW()+1)))

 

[Aladin Akyurek]

Anchor the formula to the first cell it is entered. Lets say that the first
cell is A2 and you want to copy the formula to the right:

=SUMPRODUCT((City="AUSTIN")*(Score=COLUMN()-COLUMN($A$2)))

This makes the Score test to start with 0.

 

[Frank Kabel]

Hi
in addition to the answers received you may also take a look at pivot
tables to create such reports:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[Stefan Keydel]

Many thanks to all who provided suggestions!


Regards,

Stefan

 

[Sandy Mann]

click-drag to the right for 300 cells

I've only got 256 columns in my Excel

Sandy