Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT gives incorrect total
Hi Experts:
This is my formula: =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince George"),--('SP TCS REPORT'!I2:I10000="Count of Observations"0)) The total of records should be 182 but this formula displays 179 There is no hidden data and the naming conventions for ACS-Prince George and Count of Observations are exactly the same through the entire document. Can you tell me what might be wrong with this formula? Thank you for any help you can provide, -- jeannie v |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT gives incorrect total
What is the
0 doing at the end of the second term? I suggest you get rid of this. Hope this helps. Pete On Jan 20, 12:33*am, jeannie v wrote: Hi Experts: This is my formula: =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince George"),--('SP TCS REPORT'!I2:I10000="Count of Observations"0)) The total of records should be 182 but this formula displays 179 There is no hidden data and the naming conventions for ACS-Prince George and Count of Observations are exactly the same through the entire document. Can you tell me what might be wrong with this formula? Thank you for any help you can provide, -- jeannie v |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT gives incorrect total
0 is to add all Count of Observations greater than 0....I did take the 0
out and it give me a result of "0". Any more help you can provide would be appreciated. -- jeannie v "Pete_UK" wrote: What is the 0 doing at the end of the second term? I suggest you get rid of this. Hope this helps. Pete On Jan 20, 12:33 am, jeannie v wrote: Hi Experts: This is my formula: =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince George"),--('SP TCS REPORT'!I2:I10000="Count of Observations"0)) The total of records should be 182 but this formula displays 179 There is no hidden data and the naming conventions for ACS-Prince George and Count of Observations are exactly the same through the entire document. Can you tell me what might be wrong with this formula? Thank you for any help you can provide, -- jeannie v |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT gives incorrect total
I'm using AutoFilter to pull the records for the site (ACS-Prince
George)...does this make a difference? Would there be a Macro that would work? -- jeannie v "jeannie v" wrote: 0 is to add all Count of Observations greater than 0....I did take the 0 out and it give me a result of "0". Any more help you can provide would be appreciated. -- jeannie v "Pete_UK" wrote: What is the 0 doing at the end of the second term? I suggest you get rid of this. Hope this helps. Pete On Jan 20, 12:33 am, jeannie v wrote: Hi Experts: This is my formula: =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince George"),--('SP TCS REPORT'!I2:I10000="Count of Observations"0)) The total of records should be 182 but this formula displays 179 There is no hidden data and the naming conventions for ACS-Prince George and Count of Observations are exactly the same through the entire document. Can you tell me what might be wrong with this formula? Thank you for any help you can provide, -- jeannie v |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT gives incorrect total
Maybe it's a typo.
If the typo is an extra space character, you could use this version of your formula: =SUMPRODUCT(--(trim('SP TCS REPORT'!H2:H10000)="ACS-Prince George"), --(trim('SP TCS REPORT'!I2:I10000)="Count of Observations")) If this gives you 182, I'd take the time to fix those cells with the typos. jeannie v wrote: Hi Experts: This is my formula: =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince George"),--('SP TCS REPORT'!I2:I10000="Count of Observations"0)) The total of records should be 182 but this formula displays 179 There is no hidden data and the naming conventions for ACS-Prince George and Count of Observations are exactly the same through the entire document. Can you tell me what might be wrong with this formula? Thank you for any help you can provide, -- jeannie v -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT gives incorrect total
Thank you Dave for the formula, however, it doesn't work...It displays "0"..
Any other ideas that would help? I appreciate any suggestions, -- jeannie v "Dave Peterson" wrote: Maybe it's a typo. If the typo is an extra space character, you could use this version of your formula: =SUMPRODUCT(--(trim('SP TCS REPORT'!H2:H10000)="ACS-Prince George"), --(trim('SP TCS REPORT'!I2:I10000)="Count of Observations")) If this gives you 182, I'd take the time to fix those cells with the typos. jeannie v wrote: Hi Experts: This is my formula: =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince George"),--('SP TCS REPORT'!I2:I10000="Count of Observations"0)) The total of records should be 182 but this formula displays 179 There is no hidden data and the naming conventions for ACS-Prince George and Count of Observations are exactly the same through the entire document. Can you tell me what might be wrong with this formula? Thank you for any help you can provide, -- jeannie v -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT gives incorrect total
And what is "Count of Observations"?
The way I read the formula you were looking for this text in column I AND for "ACS-Prince George" to be in column H and you wanted to count how often this happened. Pete On Jan 20, 1:38*am, jeannie v wrote: 0 is to add all Count of Observations greater than 0....I did take the 0 out and it give me a result of "0". Any more help you can provide would be appreciated. -- jeannie v "Pete_UK" wrote: What is the 0 doing at the end of the second term? I suggest you get rid of this. Hope this helps. Pete On Jan 20, 12:33 am, jeannie v wrote: Hi Experts: This is my formula: =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince George"),--('SP TCS REPORT'!I2:I10000="Count of Observations"0)) The total of records should be 182 but this formula displays 179 There is no hidden data and the naming conventions for ACS-Prince George and Count of Observations are exactly the same through the entire document.. Can you tell me what might be wrong with this formula? Thank you for any help you can provide, -- jeannie v- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT gives incorrect total
Oh...I aplogize for the confusion...No the Count of Observations is Column I
and will have a number of Observations in a record. I want to add the number of observations in each record....I could be 1, 2, 3, 4 or more observations for a single record. It want it to total the # of observations in each "I" Cell and give me a total. Does that make any sense? -- jeannie v "Pete_UK" wrote: And what is "Count of Observations"? The way I read the formula you were looking for this text in column I AND for "ACS-Prince George" to be in column H and you wanted to count how often this happened. Pete On Jan 20, 1:38 am, jeannie v wrote: 0 is to add all Count of Observations greater than 0....I did take the 0 out and it give me a result of "0". Any more help you can provide would be appreciated. -- jeannie v "Pete_UK" wrote: What is the 0 doing at the end of the second term? I suggest you get rid of this. Hope this helps. Pete On Jan 20, 12:33 am, jeannie v wrote: Hi Experts: This is my formula: =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince George"),--('SP TCS REPORT'!I2:I10000="Count of Observations"0)) The total of records should be 182 but this formula displays 179 There is no hidden data and the naming conventions for ACS-Prince George and Count of Observations are exactly the same through the entire document.. Can you tell me what might be wrong with this formula? Thank you for any help you can provide, -- jeannie v- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT gives incorrect total
On Sat, 19 Jan 2008 16:33:01 -0800, jeannie v
wrote: Hi Experts: This is my formula: =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince George"),--('SP TCS REPORT'!I2:I10000="Count of Observations"0)) The total of records should be 182 but this formula displays 179 There is no hidden data and the naming conventions for ACS-Prince George and Count of Observations are exactly the same through the entire document. Can you tell me what might be wrong with this formula? Thank you for any help you can provide, What might be the possible content of, let us say, 'SP TCS REPORT'!I2 and what are you looking for? As written, this portion of your formula: ('SP TCS REPORT'!I2:I10000="Count of Observations"0) will always return a TRUE. When Excel evaluates the formula, it does so from left to right; so you might evaluate (short form) I2="Count of Observations" This will return either FALSE or TRUE depending on whether that phrase is the contents of the cell. The next step is to evaluate either FALSE0 or TRUE0 In either case, the evaluation will be TRUE. This doesn't make sense as the section, as written, seems entirely superfluous. You would probably get the same result with the formula: =COUNTIF('SP TCS REPORT'!H2:H10000,"CS-Prince George") --ron |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT gives incorrect total
On Sat, 19 Jan 2008 16:33:01 -0800, jeannie v
wrote: Hi Experts: This is my formula: =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince George"),--('SP TCS REPORT'!I2:I10000="Count of Observations"0)) The total of records should be 182 but this formula displays 179 There is no hidden data and the naming conventions for ACS-Prince George and Count of Observations are exactly the same through the entire document. Can you tell me what might be wrong with this formula? Thank you for any help you can provide, After reading some of your other responses, I wonder if what you really want is: =sumif('SP TCS REPORT'!H2:H10000,"ACS-Prince George",'SP TCS REPORT'!I2:I10000) --ron |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT gives incorrect total
Thank you all...you got me thinking and...I figured out what I was doing
wrong....this is the correct formula to get the correct result. =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince George"),--('SP TCS REPORT'!I2:I10000)) -- jeannie v "Ron Rosenfeld" wrote: On Sat, 19 Jan 2008 16:33:01 -0800, jeannie v wrote: Hi Experts: This is my formula: =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince George"),--('SP TCS REPORT'!I2:I10000="Count of Observations"0)) The total of records should be 182 but this formula displays 179 There is no hidden data and the naming conventions for ACS-Prince George and Count of Observations are exactly the same through the entire document. Can you tell me what might be wrong with this formula? Thank you for any help you can provide, After reading some of your other responses, I wonder if what you really want is: =sumif('SP TCS REPORT'!H2:H10000,"ACS-Prince George",'SP TCS REPORT'!I2:I10000) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Incorrect total of calculated field in pivot table | Excel Discussion (Misc queries) | |||
SUMPRODUCT is giving incorrect number | Excel Discussion (Misc queries) | |||
My autosum total is incorrect - what is wrong? | Excel Discussion (Misc queries) | |||
Anyone seen Pivot Tables with incorrect detail but correct total? | Excel Discussion (Misc queries) | |||
Incorrect total | Excel Worksheet Functions |