SUMPRODUCT Help

[RT]

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.

 

[Bernard Liengme]

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(P210),--ISNUMBER(SEARCH(P210,"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

 

[Joe User]

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 P22000.

The way you wrote, you are searching for whatever is in each of P22000 in
"F". That will return TRUE (1) for any of P22000 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 P22000 contains "F" or
"f" anywhere with the cell contents. If that is your intention, fine. But
if each of P22000 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]

Joe, I didn't have any blanks in P22000, 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 (P22000 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]

Joe, I didn't have any blanks in P22000,
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",P22000). 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",P22000)))

or if you prefer:

=SUMPRODUCT(--(J2:J2000<>""), --(J2:J2000>=0),
--ISNUMBER(SEARCH("F",P22000)))


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

 

[RT]

Thanks Joe. I replaced my formula with this:

=SUMPRODUCT((J2:J2000<>"")*(J2:J2000>=0)*ISNUMBER(SEARCH("F",P22000)))

and it works perfectly.

Joe, I didn't have any blanks in P22000,
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",P22000). 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",P22000)))

or if you prefer:

=SUMPRODUCT(--(J2:J2000<>""), --(J2:J2000>=0),
--ISNUMBER(SEARCH("F",P22000)))


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

Joe, I didn't have any blanks in P22000, 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 (P22000 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