CONCATENATE within SUMPRODUCT

[Icarus]

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()))*(F10: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.

 

[Bob Phillips]

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


--
HTH

Bob

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

 

[Icarus]

Thank you. You Rock!

What is the "T" function doing?

 

[RagDyeR]

The "T" is for Text.

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

 

[RagDyeR]

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!
=====================================================


The "T" is for Text.

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

 

[Peo Sjoblom]

Plus it is not volatile so I think it is preferable

--


Regards,


Peo Sjoblom

 

[Bob Phillips]

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:F1740=INDEX($B:$B,ROW())))

which I think is a tad better

 

[Bob Phillips]

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.

 

[PCLIVE]

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



--

 

[RagDyer]

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

 

[Harlan Grove]

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.

 

[Harlan Grove]

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

 

[RagDyer]

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.

 

[PCLIVE]

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

Regards,
Paul

--

 

[PCLIVE]

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

--