SUMPRODUCT HELP

[tina]

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

 

[Max]

.. 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))

---

 

[Bernard Liengme]

=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

 

[tina]

Thank you Max and Bernard. However, both answers still come back with zero.

 

[tina]

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

 

[RAGdyer]

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.

 

[Max]

Glad you got it working, Tina.