Sum Product

[HJ]

I'm trying to write a sumproduct formula that will look up a specific project
and P&L line item, then count the number of times there is a value in a
certain row. Some cells have no value, some have zeros but I just want to
know the number of times the cell in that row has a positive or negative
value. The columns range from D:BW so the max I could have would be 72. The
formula I have now is:

SUMPRODUCT((Master!$A$2:$A$2000=$B$4)*(Master!$B$2:$B$2000=$B52)*(COUNTIF(Master!$D$2:$BW$2000,">0")))

The countif formula is giving me an answer of 20,100 where I'm looking for
the number 40.

Can anyone help guide me in the right direction?

Thanks much.

 

[RagDyer]

Since you said "positive OR negative",
Your ">0" should perhaps be "<>0",
So try this:

=SUMPRODUCT((Master!$A$2:$A$2000=$B$4)*(Master!$B$2:$B$2000=$B52)*(Master!$D
$2:$BW$2000<>0))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[HJ]

I tried the <>0 first and it gave me a huge number so I tried >0 to see if I
could get closer. I checked the data and there are no negatives for this
particular row but there are in other projects. In your formula below, how
does Excel know to count?

 

[RagDyer]

You never stated if you got it to work for you or not?

Check out this link for explanations:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Alan]

Hi All,
I just read the link posted by RagDyer below, it refers to the late Frank
Kabel, is Frank no longer with us?
It would appear not, if this is so, respect and sympathy to you and yours
Frank,
Regards,
Alan.