Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Formula Help
I have entered the following formula and I have an issue where it will not
count the "21" entries. Can the formula work with alpha and numerics used in the same column? If so, what am I doing wrong? =SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001="21"),(C9: C10001="D")*(E9:E10001)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Formula Help
Assuming the "21" values are actually numbers, not text, try:
=SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001=21), --(C9:C10001="D"), E9:E10001) In article , CLM wrote: I have entered the following formula and I have an issue where it will not count the "21" entries. Can the formula work with alpha and numerics used in the same column? If so, what am I doing wrong? =SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001="21"),(C9: C10001="D")*(E9:E10001)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Formula Help
CLM,
What are you trying to do with the first part of the equation? It looks like you're trying to add the array with itself. Per the Help the SUMPRODUCT function treats nonnumeric entries as if they were zeros. The second part of the equation actually works because you're multiplying the two arrays together. Since you're doing this the "D" entries are treated like a value of "1". Are you getting any answer other than "0" with this equation because that is all I get as when I tried it out? -- Tim Murphy "JE McGimpsey" wrote: Assuming the "21" values are actually numbers, not text, try: =SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001=21), --(C9:C10001="D"), E9:E10001) In article , CLM wrote: I have entered the following formula and I have an issue where it will not count the "21" entries. Can the formula work with alpha and numerics used in the same column? If so, what am I doing wrong? =SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001="21"),(C9: C10001="D")*(E9:E10001)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Formula Help
Since SUMPRODUCT is an array formula (even though it doesn't require
CTRL-SHIFT-ENTER), (A9:A10001="ZQ")+(A9:A10001=21) will return a 9992 element array of ones and zeros - 1 if the corresponding cell contains either "ZQ" or 21, 0 if not. Internally, each term returns an array of TRUE/FALSE, but XL coerces TRUE/FALSE to 1/0 when using a math operator like "+". In article , vttanker wrote: CLM, What are you trying to do with the first part of the equation? It looks like you're trying to add the array with itself. Per the Help the SUMPRODUCT function treats nonnumeric entries as if they were zeros. The second part of the equation actually works because you're multiplying the two arrays together. Since you're doing this the "D" entries are treated like a value of "1". Are you getting any answer other than "0" with this equation because that is all I get as when I tried it out? -- Tim Murphy "JE McGimpsey" wrote: Assuming the "21" values are actually numbers, not text, try: =SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001=21), --(C9:C10001="D"), E9:E10001) In article , CLM wrote: I have entered the following formula and I have an issue where it will not count the "21" entries. Can the formula work with alpha and numerics used in the same column? If so, what am I doing wrong? =SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001="21"),(C9: C10001="D")*(E9:E10001)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Formula Help
Jim, CLM,
I didn't look at it that way, but I agree. As written below it works. =SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001=21),(C9:C1 0001="D")*(E9:E10001)) -- Tim Murphy "JE McGimpsey" wrote: Since SUMPRODUCT is an array formula (even though it doesn't require CTRL-SHIFT-ENTER), (A9:A10001="ZQ")+(A9:A10001=21) will return a 9992 element array of ones and zeros - 1 if the corresponding cell contains either "ZQ" or 21, 0 if not. Internally, each term returns an array of TRUE/FALSE, but XL coerces TRUE/FALSE to 1/0 when using a math operator like "+". In article , vttanker wrote: CLM, What are you trying to do with the first part of the equation? It looks like you're trying to add the array with itself. Per the Help the SUMPRODUCT function treats nonnumeric entries as if they were zeros. The second part of the equation actually works because you're multiplying the two arrays together. Since you're doing this the "D" entries are treated like a value of "1". Are you getting any answer other than "0" with this equation because that is all I get as when I tried it out? -- Tim Murphy "JE McGimpsey" wrote: Assuming the "21" values are actually numbers, not text, try: =SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001=21), --(C9:C10001="D"), E9:E10001) In article , CLM wrote: I have entered the following formula and I have an issue where it will not count the "21" entries. Can the formula work with alpha and numerics used in the same column? If so, what am I doing wrong? =SUMPRODUCT((A9:A10001="ZQ")+(A9:A10001="21"),(C9: C10001="D")*(E9:E10001)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Array formula for SUMPRODUCT | Excel Worksheet Functions | |||
Array formula and multiplying conditions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |