Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-11-2020, 03:02 AM
shabbaranks shabbaranks is offline SUMIF Doesn't return total amount? Windows 7 64bit SUMIF Doesn't return total amount? Office 2007
Advanced Beginner
SUMIF Doesn't return total amount?
 
Join Date: Mar 2011
Posts: 89
shabbaranks is on a distinguished road
Default SUMIF Doesn't return total amount?

Hi - Im trying to sumif a total as per below

Code:
=SUMIF('MCS Data'!C:C,'Live Data'!B2,MCS_Data[Total Raised])
The total when I manually check is 50 but the SUMIF is coming back as 41 - any ideas why this wouldnt be working?



Thanks
Reply With Quote
  #2  
Old 12-11-2020, 07:02 AM
Purfleet Purfleet is offline SUMIF Doesn't return total amount? Windows 10 SUMIF Doesn't return total amount? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Not with out seeing the worksheet - can you upload?
Reply With Quote
  #3  
Old 12-11-2020, 07:17 AM
ArviLaanemets ArviLaanemets is offline SUMIF Doesn't return total amount? Windows 8 SUMIF Doesn't return total amount? Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Some values in range are really texts.

This may happen when you enter data into cells formatted as text, and change format afterwards. Or when you copy data from elsewhere, and use Paste instead PasteSpecial -Values (this may change cell format to text for various reasons). Simply formatting the range with such values displays them like they were numbers, but really they remain texts. To make the new format applied, you have to edit every such cell.

To check this, you remove alignment from range and enter a suspect cell in edit mode (double-click or select cell and press F2). When the value is numeric, it will be right aligned, when text, then left aligned.

When there is lot of data, it's easier simply correct issue. Format your range as General, or Numeric. Enter 1 into some unused cell (formatted as General), and copy the cell. Select your range, and then PasteSpecial - Multiply. This updates all cells in selected range leaving all values same.

Another issue is, that you use 2 different ranges from sheet 'MCS Data', C:C and Table column MCS_Data[Total Raised]. MS recommends to have all ranges used with SUMIF() or SUMIFS() of same dimension. Ignoring this may affect the result.
Reply With Quote
  #4  
Old 12-14-2020, 02:32 AM
shabbaranks shabbaranks is offline SUMIF Doesn't return total amount? Windows 7 64bit SUMIF Doesn't return total amount? Office 2007
Advanced Beginner
SUMIF Doesn't return total amount?
 
Join Date: Mar 2011
Posts: 89
shabbaranks is on a distinguished road
Default

Hi
Thanks for your replies - its strange as I re-copied the data and now its working. As I said I knew my formula was correct as I used it in a test - thanks guys!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMIF Doesn't return total amount? SUMIF with Or Lluewhyn Excel 6 02-17-2017 10:48 AM
Another SUMIF OTPM Excel 6 06-06-2016 03:00 AM
VBA sumif Nisio07 Excel Programming 0 02-26-2016 07:53 AM
Force Kiosk published PPT to return to a specific slide after x amount of minutes jasoomian PowerPoint 1 07-31-2010 03:09 AM
automatic quantity updation of total amount is restricted. aligahk06 Excel 0 05-23-2010 08:39 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:38 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