Formula Question

[RF]

I have the following formula in cell E2

=SUMPRODUCT(($B$395:$B$2043=1)*($G$395:$G$2043>0))

I have the same formula (almost) in cells E2 through U2

I want to drag this formula across those 17 cells but I need to change
the =1 to =2, for cell F2, and =3 for G2, and =4 for F2, etc.

Is there an easy way to do that other than going through each formula
and changing it by hand?

Thanks

 

[Ron Rosenfeld]

I have the following formula in cell E2

=SUMPRODUCT(($B$395:$B$2043=1)*($G$395:$G$2043>0))

I have the same formula (almost) in cells E2 through U2

I want to drag this formula across those 17 cells but I need to change
the =1 to =2, for cell F2, and =3 for G2, and =4 for F2, etc.

Is there an easy way to do that other than going through each formula
and changing it by hand?

Thanks

I presume the =4 for F2 was a typo.

Replace the =1 with =columns($A:A)

As you drag it across, it will adjust.
--ron

 

[Sandy Mann]

Try:

=SUMPRODUCT(($B$395:$B$2043=COLUMN(E1)-COLUMN($D$1))*($G$395:$G$2043>0))


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Replace the =1 with =columns($A:A)

Won't that return wrong numbers if a new column B, C or D is inserted?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Peo Sjoblom]

No, that's the beauty of using columns/rows

You don't need to anchor them like you do with column/row where you usually
subtract to offset whenever a column/row is inserted


--
Regards,

Peo Sjoblom

 

[Ron Rosenfeld]

Won't that return wrong numbers if a new column B, C or D is inserted?

That would depend on what the OP wants if he inserts columns. He might not
want it to change to the right.
--ron

 

[Pete_UK]

If you change it to COLUMN(A1), this will return 1 in the first cell.
However, when copied across this will become COLUMN(B1) and will
return 2 in the next column, then COLUMN(C1), returning 3, in the next
column, and so on.

Hope this helps.

Pete

 

[Sandy Mann]

No, that's the beauty of using columns/rows

You don't need to anchor them like you do with column/row where you
usually subtract to offset whenever a column/row is inserted

Not for me it doesn't in XL97. If I enter:

=COLUMNS($A:A)

In column E and drag it across to Column J it returns 1, 2,3, 4, 5, 6 as
expected but if I now insert a new column C it changes to 1, 2, 4, 5, 6, 7
(in colums F:K of course)

Is it different in later versions?

(Why do these thing always come up when its past my bedtime <g> )

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Peo Sjoblom]

You are correct, if you insert columns in C it will be skewed, if you insert
columns in A (before the column sumproduct deals with
SUMPRODUCT(($B$395:$B$2043=1) it works.

I never thought about inserting columns in-between where the formula is and
the column the formula deals with. I only use columns/rows when copying down
array formulas to get more than one value from de facto lookups or when
using offset/index to transpose certain layouts. and if you insert and
rows/columns above/to the left of those formula columns/rows work as opposed
to row/column

Now go to bed and stop embarrassing me <bg>

 

[RF]

Thanks for the help everyone. This solved my problem.