Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If + ISTEXT + SEARCH | Excel Discussion (Misc queries) | |||
IF-ISTEXT formula | Excel Discussion (Misc queries) | |||
How to: COUNTIF when ISTEXT is True? | Excel Worksheet Functions | |||
ISTEXT | Excel Discussion (Misc queries) | |||
I need help with a =sum IF ISTEXT formula. I keep getting 0. | Excel Discussion (Misc queries) |