sum array not calculating properly

[Harold Good]

Hi, I'm using the array formula below in a cell.
{=SUM((B2:B192="Salo, Steve")*(J2:J192>0))}

It works well in columns where there are data that meet the criteria, but in
this column J where there are none with values above 0, it counts all the
cells, in this case 29. Some are empty, most have negative values, none are
positive.

So it should result in a 0, but instead it results in a 29, the same number
of rows that have Steve Salo's name in column B.

Thanks for any ideas how to get this to read 0 instead of 29.

Harold

 

[Dave Peterson]

Is calculation set for automatic? Tools|Options|calculation tab in xl2003.

Any chance that the 29 rows that counted are rows that are hidden (like with an
autofilter)?

Any chance that you have non-numeric entries in J2:J192?

ps. I'd use a non-array formula like:

=sumproduct(--(b2:b192="salo, steve"),--isnumber(j2:j192),--(j2:j192>0))

 

[Earl Kiosterud]

Harold,

If there's text in any of the J cells, they'll get counted. Maybe there are some spaces or
other white stuff in the cells. Start deleting the empty-looking cells, and see what gives.

Also, you might find this function a little friendlier -- you don't have to do the
Ctrl-Shift-Enter schtick.
=SUMPRODUCT((B2:B192="Salo, Steve")*(J2:J192>0))

 

[Harold Good]

That's awesome, I dropped the formula in and it works great.

Now I'm trying to figure out what the double minuses do? Can you help me
understand that?

Thanks so much,
Harold

 

[Earl Kiosterud]

Harold,

Or, If I'm on the right track, change the formula to:
=SUM((B2:B192="Salo, Steve")*(J2:J192>0)*(ISNUMBER(J2:J192)))
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

-----------------------------------------------------------------------

 

[Dave Peterson]

I usually add this to my =sumproduct() posts, but failed here:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Harold Good]

Thanks for the pointers, lot's to learn!
Harold

 

[Harold Good]

Thanks Earl, I did get it to work with the Sumproduct solution that Dave
Peterson offered.
Yes, that white stuff in the cells is always a bit confusing. Since this
data is copied from an Access query, it likely has white stuff in them.

Thanks for your kind help,
Harold