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

Hi :slight_smile:

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 …

example sums.ods

This is pretty easy to explain without the data :frowning:

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.)

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

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

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.

Wow! Thank you so much. Working perfectly now.

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.


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