sum array not calculating properly

  • Thread starter Thread starter Harold Good
  • Start date Start date
H

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

-----------------------------------------------------------------------
 
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
 
Back
Top