SUMPRODUCT Help
 

Using the following formula, I am receiving a value of 40 more than it should
be:

=SUMPRODUCT(--($J$2:$J$2000=0),--ISNUMBER(SEARCH($P$2:$P$2000,"F")))

Is the problem with the "=" condition? I've tried stating it several
different ways, but am still getting the same problem. The formula works
perfectly if I only use "" or "=", but I need both.

SUMPRODUCT Help
 

Hard to say without seeing the data.
What your formula is doing is COUNTING how many cells in J2:J2000 have a
value that is greater than zero when the corresponding cell in column P has
text with the letter F (upper or lower case) within it.

If I paste the formula into an empty worksheet it returns a value of 1999.
Every empty cell is considered greater than 0; an the SEARCH returns 0 on
empty cells
So the array --($J$2:$J$2000=0 yields 1999 values of 1 as does the
array --ISNUMBER(SEARCH($P$2:$P$10,"F"))

Try
=SUMPRODUCT(--ISNUMBER(J2:J10), --($J$2:$J$10=0),--ISTEXT(P2:P10),--ISNUMBER(SEARCH(P2:P10,"F")))

You will need to change 10 to 2000 and make the references absolute - I
worked with a small data set to test this.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"RT" wrote in message
...
Using the following formula, I am receiving a value of 40 more than it
should
be:

=SUMPRODUCT(--($J$2:$J$2000=0),--ISNUMBER(SEARCH($P$2:$P$2000,"F")))

Is the problem with the "=" condition? I've tried stating it several
different ways, but am still getting the same problem. The formula works
perfectly if I only use "" or "=", but I need both.

SUMPRODUCT Help
 

"RT" wrote:
I am receiving a value of 40 more than it should be:
=SUMPRODUCT(--($J$2:$J$2000=0),--ISNUMBER(SEARCH($P$2:$P$2000,"F")))
Is the problem with the "=" condition?

Hard to say for sure without details. But I suspect your intention is to do
SEARCH("F",$P$2:$P$2000) -- that is, search for "F" in each of P2:P2000.

The way you wrote, you are searching for whatever is in each of P2:P2000 in
"F". That will return TRUE (1) for any of P2:P2000 that are empty or contain
the null string. So perhaps you have 40 "blank" cells in the range.

PS: Note that corrected SEARCH will return TRUE if P2:P2000 contains "F" or
"f" anywhere with the cell contents. If that is your intention, fine. But
if each of P2:P2000 contains only one letter (or none) and you want to test
if the letter is "F" or "f", you do simply:

=SUMPRODUCT(--($J$2:$J$2000=0),--($P$2:$P$2000="F"))

or

=SUMPRODUCT(($J$2:$J$2000=0)*($P$2:$P$2000="F"))


----- original message -----

"RT" wrote:
Using the following formula, I am receiving a value of 40 more than it should
be:

=SUMPRODUCT(--($J$2:$J$2000=0),--ISNUMBER(SEARCH($P$2:$P$2000,"F")))

Is the problem with the "=" condition? I've tried stating it several
different ways, but am still getting the same problem. The formula works
perfectly if I only use "" or "=", but I need both.

SUMPRODUCT Help
 

Joe, I didn't have any blanks in P2:P2000, but the last 40 cells in J2:J2000
were blank. Changing them to "-1" led me to the correct result. I update
this data everyday, and there is always less than 2000 rows of data, but the
total varies. I would prefer to not have to verify that there are no blanks
between J2:J2000 every time that I update (P2:P2000 contains a vlookup
formula, and is never changed). Would it be possible to tweak my formula so
that any blanks in J2:J2000 are not "counted" as zero?

Here's an example of my data:

(Col J) (Col P)
DAYS Responsible Group
462 P
371 #N/A
371 #N/A
370 #N/A
369 #N/A
358 #N/A
354 #N/A
349 #N/A
346 #N/A
336 #N/A
336 #N/A
336 #N/A
336 #N/A
336 #N/A
335 F
328 F
#N/A
#N/A


"Joe User" wrote:

"RT" wrote:
I am receiving a value of 40 more than it should be:
=SUMPRODUCT(--($J$2:$J$2000=0),--ISNUMBER(SEARCH($P$2:$P$2000,"F")))
Is the problem with the "=" condition?

Hard to say for sure without details. But I suspect your intention is to do
SEARCH("F",$P$2:$P$2000) -- that is, search for "F" in each of P2:P2000.

The way you wrote, you are searching for whatever is in each of P2:P2000 in
"F". That will return TRUE (1) for any of P2:P2000 that are empty or contain
the null string. So perhaps you have 40 "blank" cells in the range.

PS: Note that corrected SEARCH will return TRUE if P2:P2000 contains "F" or
"f" anywhere with the cell contents. If that is your intention, fine. But
if each of P2:P2000 contains only one letter (or none) and you want to test
if the letter is "F" or "f", you do simply:

=SUMPRODUCT(--($J$2:$J$2000=0),--($P$2:$P$2000="F"))

or

=SUMPRODUCT(($J$2:$J$2000=0)*($P$2:$P$2000="F"))


----- original message -----

"RT" wrote:
Using the following formula, I am receiving a value of 40 more than it should
be:

=SUMPRODUCT(--($J$2:$J$2000=0),--ISNUMBER(SEARCH($P$2:$P$2000,"F")))

Is the problem with the "=" condition? I've tried stating it several
different ways, but am still getting the same problem. The formula works
perfectly if I only use "" or "=", but I need both.

SUMPRODUCT Help
 

"RT" wrote:
Joe, I didn't have any blanks in P2:P2000,
but the last 40 cells in J2:J2000 were blank.
[....]
Would it be possible to tweak my formula so that
any blanks in J2:J2000 are not "counted" as zero?

See Bernard's solution.

You might want to change the relative references (e.g. J2:J2000) to absolute
references ($J$2:$J$2000). But relative references should suffice unless you
are copying the formula and want to preserve the references to those specific
ranges.

But I continue to suspect that you want SEARCH("F",P2:P2000). If that's the
case, you should not need the ISTEXT argument in Bernard's formula. It
doesn't hurt; it's just be redundant.

Also, if you are sure that J2:J2000 will only contain numbers or null
strings or they will be empty, the following should suffice (again, using
absolute references if you wish):

=SUMPRODUCT((J2:J2000<"")*(J2:J2000=0)*ISNUMBER( SEARCH("F",P2:P2000)))

or if you prefer:

=SUMPRODUCT(--(J2:J2000<""), --(J2:J2000=0),
--ISNUMBER(SEARCH("F",P2:P2000)))


----- original message -----

"RT" wrote:
Joe, I didn't have any blanks in P2:P2000, but the last 40 cells in J2:J2000
were blank. Changing them to "-1" led me to the correct result. I update
this data everyday, and there is always less than 2000 rows of data, but the
total varies. I would prefer to not have to verify that there are no blanks
between J2:J2000 every time that I update (P2:P2000 contains a vlookup
formula, and is never changed). Would it be possible to tweak my formula so
that any blanks in J2:J2000 are not "counted" as zero?

Here's an example of my data:

(Col J) (Col P)
DAYS Responsible Group
462 P
371 #N/A
371 #N/A
370 #N/A
369 #N/A
358 #N/A
354 #N/A
349 #N/A
346 #N/A
336 #N/A
336 #N/A
336 #N/A
336 #N/A
336 #N/A
335 F
328 F
#N/A
#N/A


"Joe User" wrote:

"RT" wrote:
I am receiving a value of 40 more than it should be:
=SUMPRODUCT(--($J$2:$J$2000=0),--ISNUMBER(SEARCH($P$2:$P$2000,"F")))
Is the problem with the "=" condition?

Hard to say for sure without details. But I suspect your intention is to do
SEARCH("F",$P$2:$P$2000) -- that is, search for "F" in each of P2:P2000.

The way you wrote, you are searching for whatever is in each of P2:P2000 in
"F". That will return TRUE (1) for any of P2:P2000 that are empty or contain
the null string. So perhaps you have 40 "blank" cells in the range.

PS: Note that corrected SEARCH will return TRUE if P2:P2000 contains "F" or
"f" anywhere with the cell contents. If that is your intention, fine. But
if each of P2:P2000 contains only one letter (or none) and you want to test
if the letter is "F" or "f", you do simply:

=SUMPRODUCT(--($J$2:$J$2000=0),--($P$2:$P$2000="F"))

or

=SUMPRODUCT(($J$2:$J$2000=0)*($P$2:$P$2000="F"))


----- original message -----

"RT" wrote:
Using the following formula, I am receiving a value of 40 more than it should
be:

=SUMPRODUCT(--($J$2:$J$2000=0),--ISNUMBER(SEARCH($P$2:$P$2000,"F")))

Is the problem with the "=" condition? I've tried stating it several
different ways, but am still getting the same problem. The formula works
perfectly if I only use "" or "=", but I need both.

SUMPRODUCT Help
 

Thanks Joe. I replaced my formula with this:

=SUMPRODUCT((J2:J2000<"")*(J2:J2000=0)*ISNUMBER( SEARCH("F",P2:P2000)))

and it works perfectly.

"Joe User" wrote:

"RT" wrote:
Joe, I didn't have any blanks in P2:P2000,
but the last 40 cells in J2:J2000 were blank.
[....]
Would it be possible to tweak my formula so that
any blanks in J2:J2000 are not "counted" as zero?

See Bernard's solution.

You might want to change the relative references (e.g. J2:J2000) to absolute
references ($J$2:$J$2000). But relative references should suffice unless you
are copying the formula and want to preserve the references to those specific
ranges.

But I continue to suspect that you want SEARCH("F",P2:P2000). If that's the
case, you should not need the ISTEXT argument in Bernard's formula. It
doesn't hurt; it's just be redundant.

Also, if you are sure that J2:J2000 will only contain numbers or null
strings or they will be empty, the following should suffice (again, using
absolute references if you wish):

=SUMPRODUCT((J2:J2000<"")*(J2:J2000=0)*ISNUMBER( SEARCH("F",P2:P2000)))

or if you prefer:

=SUMPRODUCT(--(J2:J2000<""), --(J2:J2000=0),
--ISNUMBER(SEARCH("F",P2:P2000)))


----- original message -----

"RT" wrote:
Joe, I didn't have any blanks in P2:P2000, but the last 40 cells in J2:J2000
were blank. Changing them to "-1" led me to the correct result. I update
this data everyday, and there is always less than 2000 rows of data, but the
total varies. I would prefer to not have to verify that there are no blanks
between J2:J2000 every time that I update (P2:P2000 contains a vlookup
formula, and is never changed). Would it be possible to tweak my formula so
that any blanks in J2:J2000 are not "counted" as zero?

Here's an example of my data:

(Col J) (Col P)
DAYS Responsible Group
462 P
371 #N/A
371 #N/A
370 #N/A
369 #N/A
358 #N/A
354 #N/A
349 #N/A
346 #N/A
336 #N/A
336 #N/A
336 #N/A
336 #N/A
336 #N/A
335 F
328 F
#N/A
#N/A


"Joe User" wrote:

"RT" wrote:
I am receiving a value of 40 more than it should be:
=SUMPRODUCT(--($J$2:$J$2000=0),--ISNUMBER(SEARCH($P$2:$P$2000,"F")))
Is the problem with the "=" condition?

Hard to say for sure without details. But I suspect your intention is to do
SEARCH("F",$P$2:$P$2000) -- that is, search for "F" in each of P2:P2000.

The way you wrote, you are searching for whatever is in each of P2:P2000 in
"F". That will return TRUE (1) for any of P2:P2000 that are empty or contain
the null string. So perhaps you have 40 "blank" cells in the range.

PS: Note that corrected SEARCH will return TRUE if P2:P2000 contains "F" or
"f" anywhere with the cell contents. If that is your intention, fine. But
if each of P2:P2000 contains only one letter (or none) and you want to test
if the letter is "F" or "f", you do simply:

=SUMPRODUCT(--($J$2:$J$2000=0),--($P$2:$P$2000="F"))

or

=SUMPRODUCT(($J$2:$J$2000=0)*($P$2:$P$2000="F"))


----- original message -----

"RT" wrote:
Using the following formula, I am receiving a value of 40 more than it should
be:

=SUMPRODUCT(--($J$2:$J$2000=0),--ISNUMBER(SEARCH($P$2:$P$2000,"F")))

Is the problem with the "=" condition? I've tried stating it several
different ways, but am still getting the same problem. The formula works
perfectly if I only use "" or "=", but I need both.