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

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

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