Sumproduct and ISTEXT?
 

I have two continually growing ranges.
I would like to establish how many times there is text in the same location
in each range - most of the values are 0.

I am guessing I need something like:

=sumproduct(--($AP$53:Offset($BG$53,0,0,$A$10),
ISTEXT),--($BH$53:Offset($BY$53,0,0,$A$10), ISTEXT))

naturally this won't work.

Sandy

Sumproduct and ISTEXT?
 

Try this:

=SUMPRODUCT(--(ISTEXT($AP$53:OFFSET($BG$53,0,0,$A$10))),--(ISTEXT($BH$53:OFFSET($BY$53,0,0,$A$10))))

--
Biff
Microsoft Excel MVP


"Sandy" wrote in message
...
I have two continually growing ranges.
I would like to establish how many times there is text in the same
location in each range - most of the values are 0.

I am guessing I need something like:

=sumproduct(--($AP$53:Offset($BG$53,0,0,$A$10),
ISTEXT),--($BH$53:Offset($BY$53,0,0,$A$10), ISTEXT))

naturally this won't work.

Sandy

Sumproduct and ISTEXT?
 

Perfect Biff
Thank you
Sandy

"T. Valko" wrote in message
...
Try this:

=SUMPRODUCT(--(ISTEXT($AP$53:OFFSET($BG$53,0,0,$A$10))),--(ISTEXT($BH$53:OFFSET($BY$53,0,0,$A$10))))

--
Biff
Microsoft Excel MVP


"Sandy" wrote in message
...
I have two continually growing ranges.
I would like to establish how many times there is text in the same
location in each range - most of the values are 0.

I am guessing I need something like:

=sumproduct(--($AP$53:Offset($BG$53,0,0,$A$10),
ISTEXT),--($BH$53:Offset($BY$53,0,0,$A$10), ISTEXT))

naturally this won't work.

Sandy

Sumproduct and ISTEXT?
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Sandy" wrote in message
...
Perfect Biff
Thank you
Sandy

"T. Valko" wrote in message
...
Try this:

=SUMPRODUCT(--(ISTEXT($AP$53:OFFSET($BG$53,0,0,$A$10))),--(ISTEXT($BH$53:OFFSET($BY$53,0,0,$A$10))))

--
Biff
Microsoft Excel MVP


"Sandy" wrote in message
...
I have two continually growing ranges.
I would like to establish how many times there is text in the same
location in each range - most of the values are 0.

I am guessing I need something like:

=sumproduct(--($AP$53:Offset($BG$53,0,0,$A$10),
ISTEXT),--($BH$53:Offset($BY$53,0,0,$A$10), ISTEXT))

naturally this won't work.

Sandy

Sumproduct and ISTEXT?
 

"T. Valko" wrote...
Try this:

=SUMPRODUCT(--(ISTEXT($AP$53:OFFSET($BG$53,0,0,$A$10))),
--(ISTEXT($BH$53:OFFSET($BY$53,0,0,$A$10))))
....

Might look a bit cleaner as

=SUMPRODUCT(--(ISTEXT(OFFSET($AP$53:$BG$53,0,0,$A$10))),
--(ISTEXT(OFFSET($BH$53:$BY$53,0,0,$A$10))))

but if there were many of these, it could be more efficient to use

=SUMPRODUCT(--(ISTEXT($AP$53:INDEX($BG$53:$BG$65536,$A$10))),
--(ISTEXT($BH$53:INDEX($BY$53:$BY$65536,0,0,$A$10))) )

which avoids volatile functions.