SUM Product alternative

andrewwhitfield

New Member
Joined
Mar 28, 2011
Messages
31
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
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
May 26, 2009
Messages
17,414
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

andrewwhitfield

New Member
Joined
Mar 28, 2011
Messages
31
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.

ISINCount of BP per ISINTCP BalanceTo Be InvestigatedResult
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
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 28, 2011
Messages
31
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
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top