I am currently using

=SUM(IF(Data!$E$2:$E$1700=2467,Data!$Q$2:$Q$1700,0))

But I would instead like to reference cell B2 of the current worksheet
rather than typing 2467. The reason being, I'd like to be able to drag this
calculation down, and have the formula in the cell just below reference cell
B3 of the current worksheet, etc.

Are there any sumproduct experts available who could steer me in the right
direction?

Gina

I think you mean that you want to do this:

=SUMPRODUCT(--(Data!$E$2:$E$1700=$B$2),--(Data!$Q$2:$Q$1700))

No need for CSE when you enter it.

Hope this helps.

Pete

On Aug 27, 3:11*pm, Gina <mail2g...@gmail.com> wrote:
> I am currently using
>
> =SUM(IF(Data!$E$2:$E$1700=2467,Data!$Q$2:$Q$1700,0))
>
> But I would instead like to reference cell B2 of the current worksheet
> rather than typing 2467. *The reason being, I'd like to be able to dragthis
> calculation down, and have the formula in the cell just below reference cell
> B3 of the current worksheet, etc.
>
> Are there any sumproduct experts available who could steer me in the right
> direction?
>
> Gina

"Pete_UK" wrote:

> I think you mean that you want to do this:
>
> =SUMPRODUCT(--(Data!$E$2:$E$1700=$B$2),--(Data!$Q$2:$Q$1700))
>
> No need for CSE when you enter it.
>
> Hope this helps.
>
> Pete
>
> On Aug 27, 3:11 pm, Gina <mail2g...@gmail.com> wrote:
> > I am currently using
> >
> > =SUM(IF(Data!$E$2:$E$1700=2467,Data!$Q$2:$Q$1700,0))
> >
> > But I would instead like to reference cell B2 of the current worksheet
> > rather than typing 2467. The reason being, I'd like to be able to drag this
> > calculation down, and have the formula in the cell just below reference cell
> > B3 of the current worksheet, etc.
> >
> > Are there any sumproduct experts available who could steer me in the right
> > direction?
> >
> > Gina
>
>

This is exactly what I meant. It returned the same value and worked great.
Thank you.

Gina

You're welcome, Gina - thanks for feeding back.

Pete

On Aug 27, 3:27*pm, Gina <mail2g...@gmail.com> wrote:
> "Pete_UK" wrote:
> > I think you mean that you want to do this:
>
> > =SUMPRODUCT(--(Data!$E$2:$E$1700=$B$2),--(Data!$Q$2:$Q$1700))
>
> > No need for CSE when you enter it.
>
> > Hope this helps.
>
> > Pete
>
> > On Aug 27, 3:11 pm, Gina <mail2g...@gmail.com> wrote:
> > > I am currently using
>
> > > =SUM(IF(Data!$E$2:$E$1700=2467,Data!$Q$2:$Q$1700,0))
>
> > > But I would instead like to reference cell B2 of the current worksheet
> > > rather than typing 2467. *The reason being, I'd like to be able to drag this
> > > calculation down, and have the formula in the cell just below reference cell
> > > B3 of the current worksheet, etc.
>
> > > Are there any sumproduct experts available who could steer me in the right
> > > direction?
>
> > > Gina
>
> This is exactly what I meant. *It returned the same value and worked great. *
> Thank you.
>
> Gina- Hide quoted text -
>
> - Show quoted text -

My reading of the OP's question was that she wanted the B2 to change to B3
when copied downwards, in which case I would have thought not
=SUMPRODUCT(--(Data!$E$2:$E$1700=$B$2),--(Data!$Q$2:$Q$1700))
but
=SUMPRODUCT(--(Data!$E$2:$E$1700=$B2),--(Data!$Q$2:$Q$1700))
--
David Biddulph

"Pete_UK" <(E-Mail Removed)> wrote in message
news:8f0e490c-6138-40c2-8575-(E-Mail Removed)...
> I think you mean that you want to do this:
>
> =SUMPRODUCT(--(Data!$E$2:$E$1700=$B$2),--(Data!$Q$2:$Q$1700))
>
> No need for CSE when you enter it.
>
> Hope this helps.
>
> Pete

> On Aug 27, 3:11 pm, Gina <mail2g...@gmail.com> wrote:
> > I am currently using
> >
> > =SUM(IF(Data!$E$2:$E$1700=2467,Data!$Q$2:$Q$1700,0))
> >
> > But I would instead like to reference cell B2 of the current worksheet
> > rather than typing 2467. The reason being, I'd like to be able to drag
> > this
> > calculation down, and have the formula in the cell just below reference
> > cell
> > B3 of the current worksheet, etc.