Solved

Incorrect sum value returned on sumifs function calculation in microsoft excel

Posted on 2014-02-05
7
3,533 Views
Last Modified: 2014-02-06
I have been getting incorrect totals returned on a sumifs calculation on random instances in Microsoft Office Excel 2013. The issue present is that we use producer price index commodity codes and assign inventory for our clients the appropriate PPI code for each unique inventory row in excel. The sumifs we use totals the $(dollars) for each PPI(Producer Price Index) commodity code in a separate sheet in order to run our Inventory Calculation software. Can anyone shed light upon the attached file, or has experienced what we are dealing wit, or why it would occur?
Example-of-sumif---countif-error.xlsx
0
Comment
Question by:Lee Richardson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 39837420
Replace the single quote ' by double quote "
Excel still sees your figures as number you want them to be string or text then let it be
check the file
gowflow
Example-of-sumif---countif-error.xlsx
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39837443
use
=SUMPRODUCT((A$5:A$8=A12)+0,B$5:B$8) for the sumif

the countif is more tricky and there's probably a more elegant solution but if you add a helper column "c" which has a 1 against every PPI entry you can use

=SUMPRODUCT((A$5:A$8=A12)+0,C$5:C$8

If you can modify the PPI code - or use a helper column again- then adding any character to the front of the code - to hide the leading 0 = would make sumif/countif work as normal
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 500 total points
ID: 39837523
I agree with regmigrant - SUMIF and COUNTIF typically don't distinguish between text and number, and will treat numbers stored as text values as the equivalent number - 2 = "02" etc. so if you need to do that use SUMPRODUCT which will distinguish.

For the COUNTIF you can use simply

=SUMPRODUCT((A$5:A$8=A19)+0)

regards, barry
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:Lee Richardson
ID: 39840021
Thank you for the advice on the SUMPRODUCT() function usage. It did fix the immediate issue. We had created a test file that was intended for showing someone via email, and had reduced the complexity file size (in order for it to be small enough to send in Microsoft Outlook as an attachment). The actual "sumifs" calculation is what created an error. I should have posted this file, but did not. It has an additional criteria which searches for the "Pool Number" column in order to populate the proper results. There are three different pools this company uses, and we can not lump the PPI codes all in as one pool. The attached file I have shows the example. I am not sure if SUMPRODUCT() would work on this sumifs error due to the fact that it has multiple parameters. Thanks for your advice, and I would happily accept any additional guidance from this error.
Dec2012DIS-TestOfSumifs.xlsx
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39840212
this is a huge file ! 23.9 mb and u expect someone to download this ???
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 39840614
You can use SUMPRODUCT for summing or counting with multiple criteria, just like SUMIFS or COUNTIFS.....but it will be slower, e.g. this formula will sum including the "Pool number" criteion

=SUMPRODUCT((UniqueList!$E$2:$E$71724=A8)*(UniqueList!$D$2:$D$71724=B8),UniqueList!$G$2:$G$71724)

regards, barry
0
 

Author Closing Comment

by:Lee Richardson
ID: 39840906
Works like a charm now; your a lifesaver, Barry! Thank you very much.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question