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 -