Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |