Incrementing a value in a function

  • Thread starter Thread starter Stefan Keydel
  • Start date Start date
S

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
 
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)))
 
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.
 
Back
Top