How to SUMIF + IF without !VALUE#
 

Hi, I,m John.
I have on time format "h,mm" next trying to add cells:

"=SUMIF(H34:H38,LEFT("se",2),K34:K38) "up to this point returns Ok,
but when adding a conditional IF... turns !VALUE#" & formulae continues; +IF(037="se",P37,)+IF(Q37="se",R37,)".
I'll appreciate any help.

How to SUMIF + IF without !VALUE#
 

hi, John !

I have on time format "h,mm" next trying to add cells:
"=SUMIF(H34:H38,LEFT("se",2),K34:K38) "up to this point returns Ok,
but when adding a conditional IF... turns !VALUE#"
& formulae continues; +IF(037="se",P37,)+IF(Q37="se",R37,)".
I'll appreciate any help.

1) note that your "sumif" part of the formula...
a) the "left" function is (apparently) over-used and it's the same as: =SUMIF(H34:H38,"se",K34:K38)
b) *IF* the intention is compare the 2 left characters within the range H3:H38 it's not working like that -???-

2) (perhaps) any or both "values" in cells P37 and R37 are "text-values" resulting from other =IF(... functions -???-

hth,
hector.

How to SUMIF + IF without !VALUE#
 

Hi,

Actually the first formula could be replaced with

=SUMIF(H34:H38,"se*",K34:K38)

be careful the * (astricks) is needed!

Then increase it to read

=SUMIF(H34:H38,"se*",K34:K38)+IF(LEFT(O37,2)="se", P37,0)+IF(LEFT(Q37,2)="se",R37,0)


Thanks,
Shane Devenshire


"JC-PS" wrote:

Hi, I,m John.
I have on time format "h,mm" next trying to add cells:

"=SUMIF(H34:H38,LEFT("se",2),K34:K38) "up to this point returns Ok,
but when adding a conditional IF... turns !VALUE#" & formulae continues; +IF(037="se",P37,)+IF(Q37="se",R37,)".
I'll appreciate any help.

How to SUMIF + IF without !VALUE#
 

"Héctor Miguel" wrote:

hi, John !

I have on time format "h,mm" next trying to add cells:
"=SUMIF(H34:H38,LEFT("se",2),K34:K38) "up to this point returns Ok,
but when adding a conditional IF... turns !VALUE#"
& formulae continues; +IF(037="se",P37,)+IF(Q37="se",R37,)".
I'll appreciate any help.

1) note that your "sumif" part of the formula...
a) the "left" function is (apparently) over-used and it's the same as: =SUMIF(H34:H38,"se",K34:K38)
b) *IF* the intention is compare the 2 left characters within the range H3:H38 it's not working like that -???-

2) (perhaps) any or both "values" in cells P37 and R37 are "text-values" resulting from other =IF(... functions -???-

hth,
hector.


Hi Hector Miguel;
I was mistaken when writing down on cell P37 or/and R37 just part
of the time value "h:mm" and left the hours, I had
":45" like example, instead of "0:45". This just make the difference
for '2) (perhaps) any or both "values" in cells P37 and R37 are "text-values"' as you mentioned.
On the same line,I changed the formulae for that of ShaneDevenshi

=SUMIF(H34:H38,"se*",K34:K38)+IF(LEFT(O37,2)="s e",P37,0)
+IF(LEFT(Q37,2)="se",R37,0)

with some arrangements on False areas of the conditionals, instead of ",0)" I set into ",TIME(0,0,))". I kept the wildcard "*" which I had
on first time when making fund with different trials.
I'll continue with others TRAILS...and thanks.