Incorrect sum value returned on sumifs function calculation in microsoft excel
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
Example-of-sumif---countif-error.xlsx
use
=SUMPRODUCT((A$5:A$8=A12)+
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)+
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
=SUMPRODUCT((A$5:A$8=A12)+
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)+
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Dec2012DIS-TestOfSumifs.xlsx
this is a huge file ! 23.9 mb and u expect someone to download this ???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works like a charm now; your a lifesaver, Barry! Thank you very much.
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