CONCATENATE within SUMPRODUCT
 

I have a formula in Excel 2003:

=SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858))

Which returns the number of rows in my worksheet range where the value
in column "E" equals the value in cell "A1858" AND the value in column
"F" equals the value in cell "B1858". This formula is working well,
but not 100% of what I need.

I'd like to declare the "A1858" and "B1858" portions of the function
on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW())
instead.

So the resulting formula would be:

=SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",ROW())))

However, when I do this, I get a result of 0, which is wrong.

Can anyone please help me debug this error?

Thank you.

CONCATENATE within SUMPRODUCT
 

=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&ROW()))))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Icarus" wrote in message
...
I have a formula in Excel 2003:

=SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858))

Which returns the number of rows in my worksheet range where the value
in column "E" equals the value in cell "A1858" AND the value in column
"F" equals the value in cell "B1858". This formula is working well,
but not 100% of what I need.

I'd like to declare the "A1858" and "B1858" portions of the function
on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW())
instead.

So the resulting formula would be:

=SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",ROW())))

However, when I do this, I get a result of 0, which is wrong.

Can anyone please help me debug this error?

Thank you.

CONCATENATE within SUMPRODUCT
 

Thank you. You Rock!

What is the "T" function doing?

On Jul 11, 11:12*am, "Bob Phillips" wrote:
=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO*W()))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Icarus" wrote in message

...



I have a formula in Excel 2003:

=SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858))

Which returns the number of rows in my worksheet range where the value
in column "E" equals the value in cell "A1858" AND the value in column
"F" equals the value in cell "B1858". *This formula is working well,
but not 100% of what I need.

I'd like to declare the "A1858" and "B1858" portions of the function
on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW())
instead.

So the resulting formula would be:

=SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R*OW())))

However, when I do this, I get a result of 0, which is wrong.

Can anyone please help me debug this error?

Thank you.- Hide quoted text -

- Show quoted text -

CONCATENATE within SUMPRODUCT
 

The "T" is for Text.

If you had numeric data in the columns, you'd need an "N" instead.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Icarus" wrote in message
...
Thank you. You Rock!

What is the "T" function doing?

On Jul 11, 11:12 am, "Bob Phillips" wrote:
=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO*W()))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Icarus" wrote in message

...



I have a formula in Excel 2003:

=SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858))

Which returns the number of rows in my worksheet range where the value
in column "E" equals the value in cell "A1858" AND the value in column
"F" equals the value in cell "B1858". This formula is working well,
but not 100% of what I need.

I'd like to declare the "A1858" and "B1858" portions of the function
on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW())
instead.

So the resulting formula would be:

=SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R*OW())))

However, when I do this, I get a result of 0, which is wrong.

Can anyone please help me debug this error?

Thank you.- Hide quoted text -

- Show quoted text -

CONCATENATE within SUMPRODUCT
 

If you had *mixed* data in your columns (text and numbers in *same* column),
you could try this:

=SUMPRODUCT((E10:E1740=INDEX($A$1:$A$10000,ROW())) *(F10:F1740=INDEX($B$1:$B$10000,ROW())))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"RagDyeR" wrote in message
...
The "T" is for Text.

If you had numeric data in the columns, you'd need an "N" instead.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Icarus" wrote in message
...
Thank you. You Rock!

What is the "T" function doing?

On Jul 11, 11:12 am, "Bob Phillips" wrote:
=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO*W()))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Icarus" wrote in message

...



I have a formula in Excel 2003:

=SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858))

Which returns the number of rows in my worksheet range where the value
in column "E" equals the value in cell "A1858" AND the value in column
"F" equals the value in cell "B1858". This formula is working well,
but not 100% of what I need.

I'd like to declare the "A1858" and "B1858" portions of the function
on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW())
instead.

So the resulting formula would be:

=SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R*OW())))

However, when I do this, I get a result of 0, which is wrong.

Can anyone please help me debug this error?

Thank you.- Hide quoted text -

- Show quoted text -

CONCATENATE within SUMPRODUCT
 

Plus it is not volatile so I think it is preferable

--


Regards,


Peo Sjoblom

"RagDyeR" wrote in message
...
If you had *mixed* data in your columns (text and numbers in *same*
column),
you could try this:

=SUMPRODUCT((E10:E1740=INDEX($A$1:$A$10000,ROW())) *(F10:F1740=INDEX($B$1:$B$10000,ROW())))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"RagDyeR" wrote in message
...
The "T" is for Text.

If you had numeric data in the columns, you'd need an "N" instead.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Icarus" wrote in message
...
Thank you. You Rock!

What is the "T" function doing?

On Jul 11, 11:12 am, "Bob Phillips" wrote:
=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO*W()))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Icarus" wrote in message

...



I have a formula in Excel 2003:

=SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858))

Which returns the number of rows in my worksheet range where the value
in column "E" equals the value in cell "A1858" AND the value in column
"F" equals the value in cell "B1858". This formula is working well,
but not 100% of what I need.

I'd like to declare the "A1858" and "B1858" portions of the function
on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW())
instead.

So the resulting formula would be:

=SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R*OW())))

However, when I do this, I get a result of 0, which is wrong.

Can anyone please help me debug this error?

Thank you.- Hide quoted text -

- Show quoted text -

CONCATENATE within SUMPRODUCT
 

I thought of that as well, but much as I dislike INDIRECT, I didn't like the
idea of another lookup.

I think that was probably a wrong call on my part (as does Peo <g), but my
formula didn't restrict that lookup range

=SUMPRODUCT((E10:E1740=INDEX($A:$A,ROW()))*(F10:F1 740=INDEX($B:$B,ROW())))

which I think is a tad better

--
__________________________________
HTH

Bob

"RagDyeR" wrote in message
...
If you had *mixed* data in your columns (text and numbers in *same*
column),
you could try this:

=SUMPRODUCT((E10:E1740=INDEX($A$1:$A$10000,ROW())) *(F10:F1740=INDEX($B$1:$B$10000,ROW())))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"RagDyeR" wrote in message
...
The "T" is for Text.

If you had numeric data in the columns, you'd need an "N" instead.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Icarus" wrote in message
...
Thank you. You Rock!

What is the "T" function doing?

On Jul 11, 11:12 am, "Bob Phillips" wrote:
=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO*W()))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Icarus" wrote in message

...



I have a formula in Excel 2003:

=SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858))

Which returns the number of rows in my worksheet range where the value
in column "E" equals the value in cell "A1858" AND the value in column
"F" equals the value in cell "B1858". This formula is working well,
but not 100% of what I need.

I'd like to declare the "A1858" and "B1858" portions of the function
on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW())
instead.

So the resulting formula would be:

=SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R*OW())))

However, when I do this, I get a result of 0, which is wrong.

Can anyone please help me debug this error?

Thank you.- Hide quoted text -

- Show quoted text -

CONCATENATE within SUMPRODUCT
 

It was introduced to reduce the single cell array that

INDIRECT("A"&(ROW()))

creates, but as you point out, it only allows text values, and I have no
idea where I got the idea it was text values. I would have been better to
use

INDIRECT("A"&MIN(ROW()))

if using this form at all.

--
__________________________________
HTH

Bob

"RagDyeR" wrote in message
...
The "T" is for Text.

If you had numeric data in the columns, you'd need an "N" instead.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Icarus" wrote in message
...
Thank you. You Rock!

What is the "T" function doing?

On Jul 11, 11:12 am, "Bob Phillips" wrote:
=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO*W()))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Icarus" wrote in message

...



I have a formula in Excel 2003:

=SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858))

Which returns the number of rows in my worksheet range where the value
in column "E" equals the value in cell "A1858" AND the value in column
"F" equals the value in cell "B1858". This formula is working well,
but not 100% of what I need.

I'd like to declare the "A1858" and "B1858" portions of the function
on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW())
instead.

So the resulting formula would be:

=SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R*OW())))

However, when I do this, I get a result of 0, which is wrong.

Can anyone please help me debug this error?

Thank you.- Hide quoted text -

- Show quoted text -

CONCATENATE within SUMPRODUCT
 

Just out of curiosity, what are your hang-ups about INDIRECT?



--

"Bob Phillips" wrote in message
...
I thought of that as well, but much as I dislike INDIRECT, I didn't like
the idea of another lookup.

I think that was probably a wrong call on my part (as does Peo <g), but
my formula didn't restrict that lookup range

=SUMPRODUCT((E10:E1740=INDEX($A:$A,ROW()))*(F10:F1 740=INDEX($B:$B,ROW())))

which I think is a tad better

--
__________________________________
HTH

Bob

"RagDyeR" wrote in message
...
If you had *mixed* data in your columns (text and numbers in *same*
column),
you could try this:

=SUMPRODUCT((E10:E1740=INDEX($A$1:$A$10000,ROW())) *(F10:F1740=INDEX($B$1:$B$10000,ROW())))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"RagDyeR" wrote in message
...
The "T" is for Text.

If you had numeric data in the columns, you'd need an "N" instead.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Icarus" wrote in message
...
Thank you. You Rock!

What is the "T" function doing?

On Jul 11, 11:12 am, "Bob Phillips" wrote:
=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO*W()))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Icarus" wrote in message

...



I have a formula in Excel 2003:

=SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858))

Which returns the number of rows in my worksheet range where the value
in column "E" equals the value in cell "A1858" AND the value in column
"F" equals the value in cell "B1858". This formula is working well,
but not 100% of what I need.

I'd like to declare the "A1858" and "B1858" portions of the function
on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW())
instead.

So the resulting formula would be:

=SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R*OW())))

However, when I do this, I get a result of 0, which is wrong.

Can anyone please help me debug this error?

Thank you.- Hide quoted text -

- Show quoted text -

CONCATENATE within SUMPRODUCT
 

It must have been a good guess, since you received a positive feed-back from
the OP.<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bob Phillips" wrote in message
...
It was introduced to reduce the single cell array that

INDIRECT("A"&(ROW()))

creates, but as you point out, it only allows text values, and I have no
idea where I got the idea it was text values. I would have been better to
use

INDIRECT("A"&MIN(ROW()))

if using this form at all.

--
__________________________________
HTH

Bob

"RagDyeR" wrote in message
...
The "T" is for Text.

If you had numeric data in the columns, you'd need an "N" instead.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Icarus" wrote in message
...
Thank you. You Rock!

What is the "T" function doing?

On Jul 11, 11:12 am, "Bob Phillips" wrote:
=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO-W()))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Icarus" wrote in message

...



I have a formula in Excel 2003:

=SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858))

Which returns the number of rows in my worksheet range where the value
in column "E" equals the value in cell "A1858" AND the value in column
"F" equals the value in cell "B1858". This formula is working well,
but not 100% of what I need.

I'd like to declare the "A1858" and "B1858" portions of the function
on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW())
instead.

So the resulting formula would be:

=SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R-OW())))

However, when I do this, I get a result of 0, which is wrong.

Can anyone please help me debug this error?

Thank you.- Hide quoted text -

- Show quoted text -

CONCATENATE within SUMPRODUCT
 

"PCLIVE" wrote...
Just out of curiosity, what are your hang-ups about INDIRECT?
....

INDIRECT (and OFFSET) are volatile functions, which means formulas
that call them recalculate whenever anything else triggers
recalculation. If you have only a few (< 100) such formulas, not a big
deal. If you have many (say, 10000, as one @#$%&*! spreadsheet model
I'm forced to use does), they REALLY slow down recalculation.

So like most things in life, good in moderation, horrible in excess,
and since spreadsheets have a natural tendency towards excess, best to
avoid when possible.

CONCATENATE within SUMPRODUCT
 

"Bob Phillips" wrote...
It was introduced to reduce the single cell array that

INDIRECT("A"&(ROW()))
....

Ah, the tribulations of the syntactically deprived!

INDIRECT("RC1",0)

and just the one volatile function call.

HAND

CONCATENATE within SUMPRODUCT
 

I know this was addressed to Bob, but I believe, in general, we all feel the
same.

With all things being equal, one would try to avoid using volatiles
(Indirect being one of them) and arrays.
They use a greater amount of XL's calculating resources compared to other
functions.

There is no detectable difference between using them and regular functions
when the size of the WB is small.
When an OP is working on a record collection, or a company is creating a 50
row shipping memo (where these sizes are probably the bulk of the questions
submitted in these groups), it doesn't matter what approach is taken.

However, there is always the possibility that our suggestions will be
applied to WBs with many thousands of rows, where volatiles and arrays can
appreciably slow down the opening and re-calc times.

Since a large number of the responders here are "in the business", meaning
programming and consulting, they tend to think along the lines of LARGE
files, and almost automatically try to design suggestions with this thought
in mind.

I'm sure you've seen on numerous occasions where an alternate suggestion was
posted with the opening line stating:

"non-array"
Or
"non-volatile"

These opening times and re-calc times can be a major item.

I have personally saved, just from what I've learned in these groups, 18 to
19 minutes in opening times on some large WBs that were being used as a data
base.

True, Access would be a better choice, but with an already existing cadre of
interconnected WBs, revising the "bad" formulas was easier.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"PCLIVE" wrote in message
...
Just out of curiosity, what are your hang-ups about INDIRECT?



--

"Bob Phillips" wrote in message
...
I thought of that as well, but much as I dislike INDIRECT, I didn't like
the idea of another lookup.

I think that was probably a wrong call on my part (as does Peo <g), but
my formula didn't restrict that lookup range

=SUMPRODUCT((E10:E1740=INDEX($A:$A,ROW()))*(F10:F1 740=INDEX($B:$B,ROW())))

which I think is a tad better

--
__________________________________
HTH

Bob

"RagDyeR" wrote in message
...
If you had *mixed* data in your columns (text and numbers in *same*
column),
you could try this:

=SUMPRODUCT((E10:E1740=INDEX($A$1:$A$10000,ROW())) *(F10:F1740=INDEX($B$1:$B$10000,ROW())))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"RagDyeR" wrote in message
...
The "T" is for Text.

If you had numeric data in the columns, you'd need an "N" instead.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Icarus" wrote in message
...
Thank you. You Rock!

What is the "T" function doing?

On Jul 11, 11:12 am, "Bob Phillips" wrote:
=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO-W()))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Icarus" wrote in message

...



I have a formula in Excel 2003:

=SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858))

Which returns the number of rows in my worksheet range where the
value
in column "E" equals the value in cell "A1858" AND the value in
column
"F" equals the value in cell "B1858". This formula is working well,
but not 100% of what I need.

I'd like to declare the "A1858" and "B1858" portions of the function
on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW())
instead.

So the resulting formula would be:

=SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R-OW())))

However, when I do this, I get a result of 0, which is wrong.

Can anyone please help me debug this error?

Thank you.- Hide quoted text -

- Show quoted text -

CONCATENATE within SUMPRODUCT
 

Thanks for the very detailed insightful information. I learn more every
day.

Regards,
Paul

--

"RagDyer" wrote in message
...
I know this was addressed to Bob, but I believe, in general, we all feel
the same.

With all things being equal, one would try to avoid using volatiles
(Indirect being one of them) and arrays.
They use a greater amount of XL's calculating resources compared to other
functions.

There is no detectable difference between using them and regular functions
when the size of the WB is small.
When an OP is working on a record collection, or a company is creating a
50 row shipping memo (where these sizes are probably the bulk of the
questions submitted in these groups), it doesn't matter what approach is
taken.

However, there is always the possibility that our suggestions will be
applied to WBs with many thousands of rows, where volatiles and arrays can
appreciably slow down the opening and re-calc times.

Since a large number of the responders here are "in the business", meaning
programming and consulting, they tend to think along the lines of LARGE
files, and almost automatically try to design suggestions with this
thought in mind.

I'm sure you've seen on numerous occasions where an alternate suggestion
was posted with the opening line stating:

"non-array"
Or
"non-volatile"

These opening times and re-calc times can be a major item.

I have personally saved, just from what I've learned in these groups, 18
to 19 minutes in opening times on some large WBs that were being used as a
data base.

True, Access would be a better choice, but with an already existing cadre
of interconnected WBs, revising the "bad" formulas was easier.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"PCLIVE" wrote in message
...
Just out of curiosity, what are your hang-ups about INDIRECT?



--

"Bob Phillips" wrote in message
...
I thought of that as well, but much as I dislike INDIRECT, I didn't like
the idea of another lookup.

I think that was probably a wrong call on my part (as does Peo <g), but
my formula didn't restrict that lookup range

=SUMPRODUCT((E10:E1740=INDEX($A:$A,ROW()))*(F10:F1 740=INDEX($B:$B,ROW())))

which I think is a tad better

--
__________________________________
HTH

Bob

"RagDyeR" wrote in message
...
If you had *mixed* data in your columns (text and numbers in *same*
column),
you could try this:

=SUMPRODUCT((E10:E1740=INDEX($A$1:$A$10000,ROW())) *(F10:F1740=INDEX($B$1:$B$10000,ROW())))


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"RagDyeR" wrote in message
...
The "T" is for Text.

If you had numeric data in the columns, you'd need an "N" instead.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Icarus" wrote in message
...
Thank you. You Rock!

What is the "T" function doing?

On Jul 11, 11:12 am, "Bob Phillips" wrote:
=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO-W()))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Icarus" wrote in message

...



I have a formula in Excel 2003:

=SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858))

Which returns the number of rows in my worksheet range where the
value
in column "E" equals the value in cell "A1858" AND the value in
column
"F" equals the value in cell "B1858". This formula is working well,
but not 100% of what I need.

I'd like to declare the "A1858" and "B1858" portions of the function
on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW())
instead.

So the resulting formula would be:

=SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R-OW())))

However, when I do this, I get a result of 0, which is wrong.

Can anyone please help me debug this error?

Thank you.- Hide quoted text -

- Show quoted text -

CONCATENATE within SUMPRODUCT
 

Thanks Harlan. I was not completely aware of these affects volatile
functions could have on a workbook.

--

"Harlan Grove" wrote in message
...
"PCLIVE" wrote...
Just out of curiosity, what are your hang-ups about INDIRECT?
...

INDIRECT (and OFFSET) are volatile functions, which means formulas
that call them recalculate whenever anything else triggers
recalculation. If you have only a few (< 100) such formulas, not a big
deal. If you have many (say, 10000, as one @#$%&*! spreadsheet model
I'm forced to use does), they REALLY slow down recalculation.

So like most things in life, good in moderation, horrible in excess,
and since spreadsheets have a natural tendency towards excess, best to
avoid when possible.