SUMPRODUCT HELP
 

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

SUMPRODUCT HELP
 

.. count all the z-6868 in column B that have a 6812 in Column A.
.. (answer 1)

Maybe something like this in say C1:
=SUMPRODUCT(($B$1:$B$2325="z-6868")*($A$1:$A$2325=6812))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"tina" wrote:
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

SUMPRODUCT HELP
 

=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

SUMPRODUCT HELP
 

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

SUMPRODUCT HELP
 

I played with it some more and figured out what I was doing wrong. Thank you
so much for you help!!!!!!!!!!!!!!!!!!!

"Max" wrote:

.. count all the z-6868 in column B that have a 6812 in Column A.
.. (answer 1)

Maybe something like this in say C1:
=SUMPRODUCT(($B$1:$B$2325="z-6868")*($A$1:$A$2325=6812))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"tina" wrote:
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

SUMPRODUCT HELP
 

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

SUMPRODUCT HELP
 

Glad you got it working, Tina.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"tina" wrote in message
...
I played with it some more and figured out what I was doing wrong. Thank
you
so much for you help!!!!!!!!!!!!!!!!!!!