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

P

patriciaj

New Member
Joined
Dec 21, 2012
Messages
4
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
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)
 
Upvote 0
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)
 
Last edited:
Upvote 0
patriciaj said:
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
Click to expand...
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?
 
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD]FY12
[/TD]
[TD]FY12
[/TD]
[TD]FY13
[/TD]
[TD]FY13
[/TD]
[TD]Total
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 159"]
<colgroup><col></colgroup><tbody>[TR]
[TD]$47.80
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]$263.25
[/TD]
[TD]=SUM(IF({TRUE,FALSE,FALSE,TRUE},B3:E3,0))
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$223.90
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]=SUM(IF({TRUE,FALSE,FALSE,FALSE},B4:E4,0))
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$630.60
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]$87.75
[/TD]
[TD]=SUM(IF({TRUE,FALSE,FALSE,TRUE},B5:E5,))
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$970.80
[/TD]
[TD]#N/A
[/TD]
[TD]$404.50
[/TD]
[TD]$566.30
[/TD]
[TD]=SUM(IF({TRUE,FALSE,TRUE,TRUE},B7:E7,0))
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$1239.73
[/TD]
[TD]$2,279.66
[/TD]
[TD]$419.00
[/TD]
[TD]#N/A
[/TD]
[TD](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)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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...
 
Upvote 0
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)
 
Last edited:
Upvote 0
patriciaj said:
[TABLE="width: 500"]
<TBODY>[TR]
[TD]FY12
[/TD]
[TD]FY12
[/TD]
[TD]FY13
[/TD]
[TD]FY13
[/TD]
[TD]Total
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 159"]
<TBODY>[TR]
[TD]$47.80
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]$263.25
[/TD]
[TD]=SUM(IF({TRUE,FALSE,FALSE,TRUE},B3:E3,0))
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$223.90
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]=SUM(IF({TRUE,FALSE,FALSE,FALSE},B4:E4,0))
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$630.60
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]$87.75
[/TD]
[TD]=SUM(IF({TRUE,FALSE,FALSE,TRUE},B5:E5,))
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$970.80
[/TD]
[TD]#N/A
[/TD]
[TD]$404.50
[/TD]
[TD]$566.30
[/TD]
[TD]=SUM(IF({TRUE,FALSE,TRUE,TRUE},B7:E7,0))
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$1239.73
[/TD]
[TD]$2,279.66
[/TD]
[TD]$419.00
[/TD]
[TD]#N/A
[/TD]
[TD](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)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
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...
Click to expand...
Try this...

=SUMIF(B3:E3,"<1E100")
 
Upvote 0
patriciaj said:
[TABLE="width: 500"]
<tbody>[TR]
[TD]FY12
[/TD]
[TD]FY12
[/TD]
[TD]FY13
[/TD]
[TD]FY13
[/TD]
[TD]Total
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 159"]
<tbody>[TR]
[TD]$47.80
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]$263.25
[/TD]
[TD]=SUM(IF({TRUE,FALSE,FALSE,TRUE},B3:E3,0))
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$223.90
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]=SUM(IF({TRUE,FALSE,FALSE,FALSE},B4:E4,0))
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$630.60
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]$87.75
[/TD]
[TD]=SUM(IF({TRUE,FALSE,FALSE,TRUE},B5:E5,))
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$970.80
[/TD]
[TD]#N/A
[/TD]
[TD]$404.50
[/TD]
[TD]$566.30
[/TD]
[TD]=SUM(IF({TRUE,FALSE,TRUE,TRUE},B7:E7,0))
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$1239.73
[/TD]
[TD]$2,279.66
[/TD]
[TD]$419.00
[/TD]
[TD]#N/A
[/TD]
[TD](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)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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...
Click to expand...

F3, copied down:

Either...

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

Or...

=SUMIF(B3:E3,"<"&9.99E+307)
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
This seems like a numeric error to me, but does anybody have an explanation.
Replies
2
Views
263
TomCon
T
L
  • Solved
Using SUMPRODUCT and ISNUMBER to find if an array of number has a number included with special conditions
2
Replies
18
Views
1K
Peter_SSs
Peter_SSs
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
340
STARL0RD
S
S
  • Question Question
VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns
Replies
1
Views
89
sijpie
sijpie
L
  • Question Question
I'm crashing Excel with AutoFilter.Apply
Replies
1
Views
210
Anthony47
A

Forum statistics

Threads
1,223,270
Messages
6,171,102
Members
452,379
Latest member
IainTru

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top