Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-11-2012, 11:39 AM
Spanec Spanec is offline Sum Formula in the range with Numeric and NonNumeric data cells Windows XP Sum Formula in the range with Numeric and NonNumeric data cells Office 2003
Novice
Sum Formula in the range with Numeric and NonNumeric data cells
 
Join Date: Jan 2012
Posts: 7
Spanec is on a distinguished road
Default Sum Formula in the range with Numeric and NonNumeric data cells

I have a cell range that using a LOOKUP and =OFFSET(..,MATCH....). some of the values returned are #N/A, which is expected. I want to make u SUM to all cells in that range in the end of the column (that do not contain the result #N/A) but i can not!
Reply With Quote
  #2  
Old 01-11-2012, 12:36 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Sum Formula in the range with Numeric and NonNumeric data cells Windows XP Sum Formula in the range with Numeric and NonNumeric data cells Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Try =SUMIF(your_range,"<>#N/A",your_range)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 01-12-2012, 09:15 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Sum Formula in the range with Numeric and NonNumeric data cells Windows 7 32bit Sum Formula in the range with Numeric and NonNumeric data cells Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Here's another way, similar to Peco's, which will ingore any error value:
Code:
=SUM(SUMIF(A2:A1000,{"<0",">0"}))
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum Formula in the range with Numeric and NonNumeric data cells How to combine different cells data in one cell? Learner7 Excel 1 07-28-2010 12:07 PM
Count range cells eliminating merge cells danbenedek Excel 0 06-15-2010 12:40 AM
Sum Formula in the range with Numeric and NonNumeric data cells Cut and paste a range of cells and preserve formatting StarWeaver Excel 1 03-02-2010 01:41 PM
Sum Formula in the range with Numeric and NonNumeric data cells Moving formula range multiple cells when moving sum over one cell FraserKitchell Excel 4 02-26-2010 10:38 AM
Sum Formula in the range with Numeric and NonNumeric data cells Range Formula aleksandr Excel 10 05-18-2009 12:14 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:24 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft