Array question re: Sum(If(ISNUMBER)....

[patriciaj]

Hi there,

I am at a loss to find the information I need to correctly proceed on a project I am working on.
I have created an array formula, which ignores #NA, to get a total for 4 columns... and this works fine.
BUT - when I copy/paste the formula to the other 1100 rows, it stays static, and thus does not change to reflect the (TRUE,FALSE,FALSE,TRUE) varied values, of the other rows.
How do I have the array formula become dynamic, because frankly I don't want to type in an individual formula for 1110+ rows, and I know there has to be a way to do this.
Any help or advice would be much appreciated.

(EXACT FORMULA is: =SUM(IF(ISNUMBER{TRUE,FALSE,TRUE,FALSE}, B6:B1208,0)) -- although technically the logical test part should (read=NEEDS TO) change, per each row.

Thanks,
P

 

[BiocideJ]

I believe this is what you mean (also using SUMPRODUCT causes this to NOT have to be array entered with CTRL+SHIFT+ENTER)

=SUMPRODUCT(--(ISNUMBER(B6:B1208)),B6:B1208)

 

[patriciaj]

Would that enable me to still ignore #NA and sum the individual numbers? (Sorry if I am being dense, it has been a long day)

 

[T. Valko]

Hi there,

I am at a loss to find the information I need to correctly proceed on a project I am working on.
I have created an array formula, which ignores #NA, to get a total for 4 columns... and this works fine.
BUT - when I copy/paste the formula to the other 1100 rows, it stays static, and thus does not change to reflect the (TRUE,FALSE,FALSE,TRUE) varied values, of the other rows.
How do I have the array formula become dynamic, because frankly I don't want to type in an individual formula for 1110+ rows, and I know there has to be a way to do this.
Any help or advice would be much appreciated.

(EXACT FORMULA is: =SUM(IF(ISNUMBER{TRUE,FALSE,TRUE,FALSE}, B6:B1208,0)) -- although technically the logical test part should (read=NEEDS TO) change, per each row.

Thanks,
P

Not clear what you're wanting to do.

Do you want the sum of 4 cells on a row?

Can you post a few rows worth of sample data and tell us what result you expect?

 

[patriciaj]

FY12	FY12	FY13	FY13	Total
$47.80 <colgroup><col></colgroup><tbody> </tbody>	#N/A	#N/A	$263.25	=SUM(IF({TRUE,FALSE,FALSE,TRUE},B3:E3,0))
$223.90	#N/A	#N/A	#N/A	=SUM(IF({TRUE,FALSE,FALSE,FALSE},B4:E4,0))
$630.60	#N/A	#N/A	$87.75	=SUM(IF({TRUE,FALSE,FALSE,TRUE},B5:E5,))
$970.80	#N/A	$404.50	$566.30	=SUM(IF({TRUE,FALSE,TRUE,TRUE},B7:E7,0))
$1239.73	$2,279.66	$419.00	#N/A	(How do I get these formulas to adjust according to the data in the columns - If I drag or copy from above, the Logical Test does not update)

<tbody>
</tbody>

I have many more rows of data below this too, but if I just drag the formulas down, they don't update accordingly...Let me know if you need me to explain further...

 

[BiocideJ]

EDIT: You posted the same time as me..
This should be what you are looking for...

=SUM(IF(ISNUMBER(B3:E3),B3:E3,0))
entered as an array formula (CTRL+SHIFT+ENTER)

 

[T. Valko]

FY12	FY12	FY13	FY13	Total
$47.80 <TBODY> </TBODY>	#N/A	#N/A	$263.25	=SUM(IF({TRUE,FALSE,FALSE,TRUE},B3:E3,0))
$223.90	#N/A	#N/A	#N/A	=SUM(IF({TRUE,FALSE,FALSE,FALSE},B4:E4,0))
$630.60	#N/A	#N/A	$87.75	=SUM(IF({TRUE,FALSE,FALSE,TRUE},B5:E5,))
$970.80	#N/A	$404.50	$566.30	=SUM(IF({TRUE,FALSE,TRUE,TRUE},B7:E7,0))
$1239.73	$2,279.66	$419.00	#N/A	(How do I get these formulas to adjust according to the data in the columns - If I drag or copy from above, the Logical Test does not update)

<TBODY>
</TBODY>

I have many more rows of data below this too, but if I just drag the formulas down, they don't update accordingly...Let me know if you need me to explain further...

Try this...

=SUMIF(B3:E3,"<1E100")

 

[Aladin Akyurek]

FY12	FY12	FY13	FY13	Total
$47.80 <tbody> </tbody>	#N/A	#N/A	$263.25	=SUM(IF({TRUE,FALSE,FALSE,TRUE},B3:E3,0))
$223.90	#N/A	#N/A	#N/A	=SUM(IF({TRUE,FALSE,FALSE,FALSE},B4:E4,0))
$630.60	#N/A	#N/A	$87.75	=SUM(IF({TRUE,FALSE,FALSE,TRUE},B5:E5,))
$970.80	#N/A	$404.50	$566.30	=SUM(IF({TRUE,FALSE,TRUE,TRUE},B7:E7,0))
$1239.73	$2,279.66	$419.00	#N/A	(How do I get these formulas to adjust according to the data in the columns - If I drag or copy from above, the Logical Test does not update)

<tbody>
</tbody>

I have many more rows of data below this too, but if I just drag the formulas down, they don't update accordingly...Let me know if you need me to explain further...

F3, copied down:

Either...

=SUM(SUMIF(B3:E3,{"<0",">0"}))

Or...

=SUMIF(B3:E3,"<"&9.99E+307)

 

[BiocideJ]

Try this...

=SUMIF(B3:E3,"<1E100")

Beautifully simple (like your signature)

 

[T. Valko]

Beautifully simple (like your signature)

Thanks!