How to determine the values?

E

Eric

Does anyone have any suggestions on how to determine the values?
There are lists of number under column T and U.
For example, I would like to determine the number of occurrence, if any 28
under T column contains 1 under U column, then return the occurrence under
column Y, On following formula, it does not work, does anyone have any
suggestions on what wrong it is?
=SUMPRODUCT((28=T2:T1252)*(1=U2:U1252),1)
Thanks in advance for any suggestions
Eric

[T]
28 1
32 2
33 2
34 1
35 1
28 3
32 3
33 5
34 4
35 5
28 1
32 5
33 4
34 3
35 2
28 1
32 5
33 4
34 3
35 2
 
J

Jacob Skaria

If you mean the number of instances where 28 and 1 are in the same row; then
try the below

=SUMPRODUCT((28=T2:T1252)*(1=U2:U1252))
 
M

Ms-Exl-Learner

=SUMPRODUCT((T1:T20=28)*(U1:U20=1))

Remember to Click Yes, if this post helps!
 
E

Eric

Thank everyone very much for suggestions
Could you please tell me what the difference is having -- before "("?
Thank everyone very much for suggestions
Eric


T. Valko said:
Try this...

=SUMPRODUCT(--(T2:T1252=28),--(U2:U1252=1))

--
Biff
Microsoft Excel MVP


Eric said:
Does anyone have any suggestions on how to determine the values?
There are lists of number under column T and U.
For example, I would like to determine the number of occurrence, if any 28
under T column contains 1 under U column, then return the occurrence under
column Y, On following formula, it does not work, does anyone have any
suggestions on what wrong it is?
=SUMPRODUCT((28=T2:T1252)*(1=U2:U1252),1)
Thanks in advance for any suggestions
Eric

[T]
28 1
32 2
33 2
34 1
35 1
28 3
32 3
33 5
34 4
35 5
28 1
32 5
33 4
34 3
35 2
28 1
32 5
33 4
34 3
35 2



.
 
D

David Biddulph

Double unary minus:
a.. http://www.mcgimpsey.com/excel/formulae/doubleneg.html
b.. http://xldynamic.com/source/xld.SUMPRODUCT.html

--
David Biddulph

Eric said:
Thank everyone very much for suggestions
Could you please tell me what the difference is having -- before "("?
Thank everyone very much for suggestions
Eric


T. Valko said:
Try this...

=SUMPRODUCT(--(T2:T1252=28),--(U2:U1252=1))

--
Biff
Microsoft Excel MVP


Eric said:
Does anyone have any suggestions on how to determine the values?
There are lists of number under column T and U.
For example, I would like to determine the number of occurrence, if any
28
under T column contains 1 under U column, then return the occurrence
under
column Y, On following formula, it does not work, does anyone have any
suggestions on what wrong it is?
=SUMPRODUCT((28=T2:T1252)*(1=U2:U1252),1)
Thanks in advance for any suggestions
Eric

[T]
28 1
32 2
33 2
34 1
35 1
28 3
32 3
33 5
34 4
35 5
28 1
32 5
33 4
34 3
35 2
28 1
32 5
33 4
34 3
35 2



.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top