| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT troubles
Hello all,
I have read many posts on this subject, and thought I figured it out. But the formula result is 0. I have data in a tab named Data. Column AA is the ticket number. Column AE is the ticket level (1,2,3 or 4). I am trying to count the number of unique ticket numbers in column AA and ticket level column AE for each ticket level in the formula. =SUMPRODUCT((Data!AE2:Data!AE100="1")/COUNTIF(Data!AA2:Data!AA100,Data!AA2:Data!AA100&"" )*(Data!AA2:Data!AA100<"")) Sample Data AA AE 1234 1 1235 1 1234 1 i would like the formula to return the value of 2 What am I doing wrong? Any assistance is greatly appreciated. David |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT troubles
Remove quotes from around the 1 UNLESS that column is TEXT: worked OK for me.
=SUMPRODUCT((Data!AE2:Data!AE100=1)/COUNTIF(Data!AA2:Data!AA100,Data!AA2:Data!AA100&"" )*(Data!AA2:Data!AA100<"")) "David" wrote: Hello all, I have read many posts on this subject, and thought I figured it out. But the formula result is 0. I have data in a tab named Data. Column AA is the ticket number. Column AE is the ticket level (1,2,3 or 4). I am trying to count the number of unique ticket numbers in column AA and ticket level column AE for each ticket level in the formula. =SUMPRODUCT((Data!AE2:Data!AE100="1")/COUNTIF(Data!AA2:Data!AA100,Data!AA2:Data!AA100&"" )*(Data!AA2:Data!AA100<"")) Sample Data AA AE 1234 1 1235 1 1234 1 i would like the formula to return the value of 2 What am I doing wrong? Any assistance is greatly appreciated. David |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT troubles
Thank you. Works fine for me too without the quotes.
"Toppers" wrote: Remove quotes from around the 1 UNLESS that column is TEXT: worked OK for me. =SUMPRODUCT((Data!AE2:Data!AE100=1)/COUNTIF(Data!AA2:Data!AA100,Data!AA2:Data!AA100&"" )*(Data!AA2:Data!AA100<"")) "David" wrote: Hello all, I have read many posts on this subject, and thought I figured it out. But the formula result is 0. I have data in a tab named Data. Column AA is the ticket number. Column AE is the ticket level (1,2,3 or 4). I am trying to count the number of unique ticket numbers in column AA and ticket level column AE for each ticket level in the formula. =SUMPRODUCT((Data!AE2:Data!AE100="1")/COUNTIF(Data!AA2:Data!AA100,Data!AA2:Data!AA100&"" )*(Data!AA2:Data!AA100<"")) Sample Data AA AE 1234 1 1235 1 1234 1 i would like the formula to return the value of 2 What am I doing wrong? Any assistance is greatly appreciated. David |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COM Add-in Troubles | Excel Discussion (Misc queries) | |||
| Chart troubles | Excel Worksheet Functions | |||
| IF troubles | Excel Worksheet Functions | |||
| Import troubles | Excel Discussion (Misc queries) | |||
| Vlookup Troubles | Excel Worksheet Functions | |||
Linear Mode
