Sum Product
 

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.

Sum Product
 

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" wrote in message
...
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(Ma
ster!$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.

Sum Product
 

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" wrote:

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" wrote in message
...
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(Ma
ster!$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.

Sum Product
 

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"HJ" wrote in message
...
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" wrote:

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" wrote in message
...
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(Ma
ster!$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.

Frank Kabel
 

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.
"RagDyer" wrote in message
...
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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"HJ" wrote in message
...
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" wrote:

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" wrote in message
...
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(Ma
ster!$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.