Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
SUMPRODUCT | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct help | Excel Worksheet Functions |