sum array not calculating properly

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Harold Good - 29 Jan 2008 21:08 GMT

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

Reply to this Message

Dave Peterson - 29 Jan 2008 21:50 GMT

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

> Hi, I'm using the array formula below in a cell.
> {=SUM((B2:B192="Salo, Steve")*(J2:J192>0))}
[quoted text clipped - 10 lines]
>
> Harold

Signature

Dave Peterson

Reply to this Message

Harold Good - 29 Jan 2008 22:02 GMT

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

> Is calculation set for automatic? Tools|Options|calculation tab in
> xl2003.
[quoted text clipped - 26 lines]
>>
>> Harold

Reply to this Message

Dave Peterson - 29 Jan 2008 22:21 GMT

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

> That's awesome, I dropped the formula in and it works great.
>
[quoted text clipped - 38 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Harold Good - 29 Jan 2008 22:25 GMT

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

>I usually add this to my =sumproduct() posts, but failed here:
>
[quoted text clipped - 57 lines]
>> >
>> > Dave Peterson

Reply to this Message

Earl Kiosterud - 29 Jan 2008 22:01 GMT

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

Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

> Hi, I'm using the array formula below in a cell.
> {=SUM((B2:B192="Salo, Steve")*(J2:J192>0))}
[quoted text clipped - 9 lines]
>
> Harold

Reply to this Message

Earl Kiosterud - 29 Jan 2008 22:16 GMT

Harold,

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

Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

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

> Harold,
>
[quoted text clipped - 19 lines]
>>
>> Harold

Reply to this Message

Harold Good - 29 Jan 2008 22:28 GMT

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

> Harold,
>
[quoted text clipped - 24 lines]
>>>
>>> Harold

Reply to this Message