Summing a column with Text & numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lee Grant - 14 Dec 2007 23:17 GMT

Hi there,

I'm trying to add some values in a column.

I have a column (d3 to d50). I need to sum every third row (ie D5, D8, D11,
D17 etc.) The problem seems to be that in two rows above each third row
there may or may not be some data. I have a formula in each third row to
check if the two rows above have some data in and if so multiply them. If
not I want nothing to be displayed.

Example:

D3 contains at time 00:47:20
D4 could contain a value if inputed by the user
D5 contains =IF(OR(D3="",D4="")," ",(D3-D4)

This series of three continues to the bottom of the column.

I discovered that SUM ignores text but some some reason when I do a
=sum(d5+d8+d11+d17..etc. forumla I get a #value error. I'm guessing this is
due to the " " that is inserted into the third row cells with the formula
(in the example =IF(OR(D3="",D4="")," ",(D3-D4)).

I'm stumped and I'm hoping one of your guys can use your genius to point me
in the correct direction and point out my obvious error.

Cheers

Lee

Reply to this Message

Ron Coderre - 14 Dec 2007 23:29 GMT

Try this:

With your data structure in D3:D50

This formula will return the valid items
from every 3rd row, beginning with the
formula in D5

=SUMPRODUCT((MOD(ROW(D3:D50)-2,3)=0)*(D3:D50&0))

Note: As long as your formula inserts a space
or an empty string when items are missing,
the appended zero, above, will resolve to
a numeric zero.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

> Hi there,
>
[quoted text clipped - 25 lines]
>
> Lee

Reply to this Message

Lee Grant - 17 Dec 2007 10:07 GMT

Excellent - Many thanks (again) guys.

Just as an addition: If I changed my sheet and instead of a blank space or
empty string being used, I actually used a text string (for example 'DNR' or
'DNQ' - how could I modify Ron's formula:

=SUMPRODUCT((MOD(ROW(D3:D50)-2,3)=0)*(D3:D50&0))

to cope with this?

Cheers

Lee

> Try this:
>
[quoted text clipped - 50 lines]
>>
>> Lee

Reply to this Message

Ron Coderre - 17 Dec 2007 11:29 GMT

Try this version of the SUMPRODUCT construct:

=SUMPRODUCT(--(MOD(ROW(D3:D50)-2,3)=0),D3:D50)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

> Excellent - Many thanks (again) guys.
>
[quoted text clipped - 64 lines]
>>>
>>> Lee

Reply to this Message

Lee Grant - 18 Dec 2007 12:02 GMT

Ron,

Once again you've come up with the goods.

Many, many, many thanks.

Cheers

Lee

> Try this version of the SUMPRODUCT construct:
>
[quoted text clipped - 77 lines]
>>>>
>>>> Lee

Reply to this Message

kounoike - 15 Dec 2007 06:11 GMT

I think the reason of getting a #value error is that you use +operator like
=sum(d5+d8+d11+d17..
I think a use of something like
=SUM(D5,D8,D11,D14,D17,D20,D23,D26,D29,D32,D35,D38,D41,D44,D47,D50) will do.
But as the number of argument has a limit, so one workaround of this is a
use of array formula like {=SUM(TRANSPOSE(OFFSET(D1,ROW(2:17)*3-2,0)))}in
case of the range "D3:D50". In the case of diffrernt range e.g. "D3:D98",
use ROW(2:33)*3-2 instead of ROW(2:17)*3-2.

keizi

> Hi there,
>
[quoted text clipped - 25 lines]
>
> Lee

Reply to this Message