Ask Your Question

Why different results in calculating (sumifs, sumproduct, notiserror(find))?

asked 2020-02-14 12:03:05 +0100

inJesus gravatar image

updated 2020-02-14 20:38:39 +0100

Hi :-)

For accounting I want to have a recheck in calculation.

But I can not explain why

SUMIFS(19ie.D2:D1000;19ie.$D2:$D1000;"1") =161

does not equal exactly with

the sum of all results from a column with cells containg =SUMPRODUCT(19ie.D$2:D$1000;NOT(ISERROR(FIND($B8;19ie.$B$2:$B$1000)));19ie.$D$2:$D$1000=1) ... =158

Happy about any ideas.

Here is the corresponding file ...

C:\fakepath\example sums.ods

edit retag flag offensive close merge delete


This is pretty easy to explain without the data :-(

Opaque gravatar imageOpaque ( 2020-02-14 13:34:33 +0100 )edit

To me it looks like you are filtering one sum for a specific value in the B column, while the other is unfiltered. Also, the first formula looks like it should be an array formula but you didn't specify that it is, or not.

Can you attach to your question a file displaying the different results? Without that it is very difficult to understand what you are working with.

Remember that this is a public place. Remove any confidential data. (Most likely, only the offending formulas and the numbers in columns B and D are required to locate the cause.)

keme gravatar imagekeme ( 2020-02-14 13:36:58 +0100 )edit

And in the SUMIFS there is one condition while in SUMPRODUCT there are two.

m.a.riosv gravatar imagem.a.riosv ( 2020-02-14 13:41:29 +0100 )edit

Hi, thanks. I have reduced the file and attached it.
Happy about a good explanation (strange, as it is my own file, but somehow it works, but on the other hand I do not see why it is differing in the control sum ...) Sincere, Klaus

inJesus gravatar imageinJesus ( 2020-02-14 20:41:46 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-02-14 23:29:58 +0100

updated 2020-02-14 23:30:41 +0100

From column B, delete the spaces entered after, GS and MN.


ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more


Wow! Thank you so much. Working perfectly now.

inJesus gravatar imageinJesus ( 2020-02-15 20:43:03 +0100 )edit

answered 2020-02-14 22:10:32 +0100

m.a.riosv gravatar image

Your SUMPRODUCTs are not searching for all two first letter and are searching in the entire text no only at the beginning.

with: =SUMPRODUCT(19ie.D$2:D$1089;NOT(ISERROR(SEARCH("^"&$B4;19ie.$B$2:$B$1089)));19ie.$D$2:$D$1089=1) and all different type to search work fine (needs regular expression enable)

Take a look your file modified, including a pivot table to show also the correct result.


edit flag offensive delete link more


Thank you for contributing. As the other solution worked easily, I do not verify or test this solution.

inJesus gravatar imageinJesus ( 2020-02-15 20:45:06 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-02-14 12:03:05 +0100

Seen: 69 times

Last updated: Feb 14