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]

Perhaps

=COUNTIFS(C:C,C3,N:N,"<>0")

Sumproduct should also work if you move <>0 inside the closing bracket, but it is far less efficient.

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

 

[JoeMo]

Not sure I understand what you want. Does this work?
=SUMPRODUCT((C:C=C3)*(N:N<>0),N:N)

 

[jasonb75]

Does this work?
=SUMPRODUCT((C:C=C3)*(N:N<>0),N:N)

Wouldn't that be the same as

=SUMIFS(N:N,C:C,C3,N:N,"<>0")

or are you seeing something that I'm not?

 

[JoeMo]

Wouldn't that be the same as

=SUMIFS(N:N,C:C,C3,N:N,"<>0")

or are you seeing something that I'm not?

Yes, the same, but the title asks for a sumproduct alternative ...

 

[andrewwhitfield]

Thanks for the responses guys. having just stuck my formula into another workbook i get the result i expected. There must be something wrong elsewhere in the worksheet.

 

[jasonb75]

but the title asks for a sumproduct alternative ...

If it does the same thing then it is an alternative...

an alternative to the Sum Product formula i have been using.

 

[andrewwhitfield]

Thanks for the responses guys. having just stuck my formula into another workbook i get the result i expected. There must be something wrong elsewhere in the worksheet.

Scrap that i was talking rubbish. I think they way to do this is to ignore negatives in the sum so only a column of actual 0 values would equate to 0. for example -1 and 1 would equal 2 not 0. Any help appreciated.

I also tested the examples above and the all give me the same result i have now.

 

[jasonb75]

Perhaps

=SUMIFS(N:N,C:C,C3,N:N,">0")+ABS(SUMIFS(N:N,C:C,C3,N:N,"<0"))

 

[JoeMo]

If it does the same thing then it is an alternative...

Yes, I see that, but the SUMIFS was not offered when I posted the alternative SUMPRODUCT. You had offered COUNTIFS in post #2.