# SUM Product alternative

#### andrewwhitfield

##### New Member
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

##### Well-known Member
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.

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### JoeMo

##### MrExcel MVP
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

##### Well-known Member
I really did not understand exactly what the OP was requesting
fair point

#### andrewwhitfield

##### New Member
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

##### Well-known Member

Post 9?
Or perhaps
Excel Formula:
``=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

##### New Member
Post 9?
Or perhaps
Excel Formula:
``=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

##### Well-known Member
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).

Replies
6
Views
154
Replies
0
Views
89
Replies
4
Views
63
Replies
1
Views
59
Replies
2
Views
424

1,127,594
Messages
5,625,696
Members
416,127
Latest member
MALEPINZON

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back