summing rows with different criterias

[shpon]

I had posted this question before too and got an answer
from Dave Peterson....Thank you so much...the problem is
that my nubers are not single digits all the time...there
are times that i have single digits but sometimes i have
decimal numbers too...for example, i might have 0.25SL or
1.5V...in that case these formulas don't work...any idea?
thanks a lot in advance.

-----Original Message-----
are all the numbers single digits?

If yes, then this worked ok for me:

(I used columns A:E for my input range)

For SL:
=SUM(IF(RIGHT(A1:E1,2)="SL",--LEFT(A1:E1,1)))

For V:
=SUM(IF(RIGHT(A1:E1,1)="V",--LEFT(A1:E1,1)))

But both of these formulas are array formulas. Hit ctrl-shift-enter instead of
enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

for regular hours:
=sum(a1:e1)
(excel will ignore the text values)


--

Dave Peterson
(e-mail address removed)
.

..

 

[Bernie Deitrick]

shpon,

Still array entered:

For SL:
=SUM(IF(RIGHT(A1:E1,2)="SL",--LEFT(A1:E1,LEN(A1:E1)-2)))

For V:
=SUM(IF(RIGHT(A1:E1,1)="V",--LEFT(A1:E1,LEN(A1:E1)-1)))

HTH,
Bernie
MS Excel MVP

 

[Bernard Liengme]

I can do this only if you allow me two "helper rows".
I chose to but these on Sheet2.
I have assumed the data to be summed is in A1:F1 (changes references as
needed)
In A1 of sheet2, enter =--SUBSTITUTE(SUBSTITUTE(Sheet1!A1,"sl",""),"v","");
copy to F1.This gives numeric values.
Next use FIND to decide if the data cell has SL, V or neither. Trouble is
FIND (or SEARCH) give #VALUE! when the search is unsuccessful so we need a
lengthy formula in A2 of Sheet2:
=IF(NOT(ISERROR(FIND("sl",Sheet1!A1))),"sl",IF(NOT(ISERROR(FIND("v",Sheet1!A
1))),"v",""))
This gives "sl" or "v" or blank
In Sheet 1 where you want to add all the sl values use:
=SUMIF(Sheet2!A2:F2,"sl",Sheet2!A1:F1)
For all the v values: =SUMIF(Sheet2!A2:F2,"v",Sheet2!A1:F1)
For the 'regular' values: =SUMIF(Sheet2!A2:F2,"",Sheet2!A1:F1)

Hope this helps


Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address