SUM Product alternative

[andrewwhitfield]

I'm hoping someone can help me with an alternative to the Sum Product formula i have been using.

SUMPRODUCT(--(C:C=C3),N:N)<>0

Lets say for example the same value exist in C3, C4, C5 i get the return from the formula where the SUM of N3, N4 N5 does not equal 0. What I actually need is to identify where the value in any of N3, N4 N5 does not contain zero.

My issue being positives and negatives could net to zero currently which I need to avoid.

 

[jasonb75]

but the SUMIFS was not offered when I posted the alternative SUMPRODUCT.

I didn't say that it had been offered, the countifs suggestion was a misread of the original formula. When I asked if you were seeing something that I missed, I was asking in the context of why you stuck with sumproduct given that it would be significantly less efficient with entire columns.

 

[JoeMo]

I didn't say that it had been offered, the countifs suggestion was a misread of the original formula. When I asked if you were seeing something that I missed, I was asking in the context of why you stuck with sumproduct given that it would be significantly less efficient with entire columns.

I really did not understand exactly what the OP was requesting, so my focus was on seeing if what I offered was in the ballpark, rather than efficiency.

 

[jasonb75]

I really did not understand exactly what the OP was requesting

fair point

 

[andrewwhitfield]

I'm not sure how to upload a ss but hopefully this will help.

If you put the table below in a spreadsheet from B1 you will see the result is a blank return. This is because the Sum of column D nets to 0 what i need is to recognise that there is something other than zero in column B.

My thought was that if i could ignore the negatives the result would be 2 but i'm struggling to get this result.

ISIN	Count of BP per ISIN	TCP Balance	To Be Investigated	Result
AU000000KRM1	0​	0.00​	0​	=IF(A2<>"",A2,IF(AND(C2=0,SUMPRODUCT(--(B:B=B2),D:D)<>0,SUMPRODUCT(--(B:B=B2),E:E)=0),"Items in TCP -Fail",""))
AU000000KRM1	0​	-1.00​	1​	=IF(A2<>"",A2,IF(AND(C2=0,SUMPRODUCT(--(B:B=B2),D:D)<>0,SUMPRODUCT(--(B:B=B2),E:E)=0),"Items in TCP -Fail",""))
AU000000KRM1	0​	1.00​	-1​	=IF(A2<>"",A2,IF(AND(C2=0,SUMPRODUCT(--(B:B=B2),D:D)<>0,SUMPRODUCT(--(B:B=B2),E:E)=0),"Items in TCP -Fail",""))

 

[jasonb75]

Post 9?
Or perhaps

=IF(A2<>"",A2,IF(AND(C2=0,SUMPRODUCT(ABS(D2:E2))=0),"Items in TCP -Fail",""))

It is not clear if you want the result for each individual row, or the total for all rows. If the fail result is for non zero then you need to change the second =0 to <>0

 

[andrewwhitfield]

Post 9?
Or perhaps

=IF(A2<>"",A2,IF(AND(C2=0,SUMPRODUCT(ABS(D2:E2))=0),"Items in TCP -Fail",""))

It is not clear if you want the result for each individual row, or the total for all rows. If the fail result is for non zero then you need to change the second =0 to <>0

I need to consider the result of each row where the results in column B are identical. I also need to look at column D and E separately as I am looking at the net value in column E.

Post 9 did not work i'm afraid as it stops when the value is zero.

 

[jasonb75]

And post 15?

That returns results of -1 + 1 = 2 on a row by row basis, which is what you asked for earlier. Bearing in mind that you only used a non-working formula to show us the results, you are leaving the actual results open to our interpretation. A good example would show a variety of possible combinations with the actual results that you expect for each row, not your existing formula.

Do you want the values shown or just the fail message? In your formula sumproduct is only a logical test, not a result.
What if both columns are negative, or both are positive?
Your example only uses values of 1 and -1, what about unequal values, e.g. -2 and +3? Result of 5?

The best way to post an example is with XL2BB (link in my signature below).