The probable cause of that is your data in Column A and/or Column B *doesn't
match* the criteria that's in your formula.
Since you tried both formulas, where one checked for a text entry, and the
other for a numeric entry in Column A, it doesn't appear that it's a format
problem.
You might have trailing or leading <spaces, or some other invisible
characters imbedded in the Column A and/or Column B data.
You might try to manually key in
6012
into a row in Column A,
And
Z-6868
into the same row in Column B,
and see if that gets you a return.
You could also try the Len() function to see if there are invisible
characters.
=Len(A1)
should return a 4, if A1 displays 6012,
And
=Len(B1)
should return a 6, if B1 displays Z-6868.
--
HTH,
RD
----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
----------------------------------------------------------------------------------------
"tina" wrote in message
...
Thank you Max and Bernard. However, both answers still come back with
zero.
"Bernard Liengme" wrote:
=SUMPRODUCT(--(Sheet1'!$B$1:$B$2325="Z-6868"),--(sheet1'!$A$1:$A$2325="6012"))
Or if A has real numbers
=SUMPRODUCT(--(Sheet1'!$B$1:$B$2325="Z-6868"),--(sheet1'!$A$1:$A$2325=6012))
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
"tina" wrote in message
...
Hello,
I am trying to count one column based on what another column has. I
have
tried using a SUMPRODUCT and I either get a zero or the total amount.
Can
someone help me?
This is what it looks like. There are several other columns but these
are
the two columns I want to count. I would like to count all the z-6868
in
column B that have a 6812 in Column A. (answer 1)
Column A Column B
Row 1 6812 z-6868
Row 2 6868 z-6868
Row 3 6836 z-6868
When I use
=SUMPRODUCT((Sheet1'!$A$1:$AL$2325="Z-6868")--(sheet1'!$A$1:$AL$2325="6012"))+((sheet1'!$A$1:$AL $2325="W-6868")--(sheet1'!$A$1:$AL$2325="6012"))
value returned 3. Should be 1.
When I use
=SUMPRODUCT(('Sheet1'!$A$1:$AL$2325="Z-6868")*('Sheet1'!$A$1:$AL$2325="6012"))+(('Sheet1' !$A$1:$AL$2325="W-6868")*('Sheet1'!$A$1:$AL$2325="6012"))
the value returned is zero
I hope I was able to explain this right. Any help would be greatly
appreciated.
Thank you Tina