Summing a column with Text & numbers

[Lee Grant]

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

 

[Ron Coderre]

Try this:

With your data structure in D350

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

=SUMPRODUCT((MOD(ROW(D350)-2,3)=0)*(D350&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)

 

[kounoike]

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 "D350". In the case of diffrernt range e.g. "D398",
use ROW(2:33)*3-2 instead of ROW(2:17)*3-2.

keizi

 

[Lee Grant]

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(D350)-2,3)=0)*(D350&0))

to cope with this?

Cheers

Lee

 

[Ron Coderre]

Try this version of the SUMPRODUCT construct:

=SUMPRODUCT(--(MOD(ROW(D350)-2,3)=0),D350)

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

Regards,

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

 

[Lee Grant]

Ron,

Once again you've come up with the goods.

Many, many, many thanks.

Cheers

Lee