I have a problem in excel.my excel workbook has a sheet (1), which have number in j5. In k5 there long if formula , which calculate data from j5 , =IF(J5<1,"0",IF(J5<11,"2",IF(J5<21,"4",IF(J5<31,"6",IF(J5<41,"8",IF(J5<51,"10",IF(J5<61,"12",IF(J5<71,"14",IF(J5<81,"16",IF(J5<91,"18",IF(J5<101,"20",IF(J5<111,"22",IF(J5<121,"24",IF(J5<131,"26",IF(J5<141,"28",IF(J5<151,"30",IF(J5<161,"32",IF(J5<171,"34",IF(J5<181,"36",IF(J5<191,"38",IF(J5<201,"40",IF(J5<211,"42",IF(J5<221,"44",IF(J5<231,"46",IF(J5<241,"48",IF(J5<251,"50",IF(J5<261,"52",IF(J5<271,"54",IF(J5<281,"56",IF(J5<291,"58",IF(J5<301,"60",IF(J5<311,"62",IF(J5<321,"64",IF(J5<331,"66",IF(J5<341,"68",IF(J5<351,"70",IF(J5<361,"72",IF(J5<371,"74",IF(J5<381,"76",IF(J5<391,"78",IF(J5<401,"80",IF(J5<411,"82",IF(J5<421,"84",IF(J5<431,"86",IF(J5<441,"88",IF(J5<451,"90",IF(J5<461,"92",IF(J5<471,"94",IF(J5<481,"96",IF(J5<491,"98",IF(J5<501,"100",IF(J5<511,"102",IF(J5<521,"104",IF(J5<531,"106",IF(J5<541,"108",IF(J5<551,"110",IF(J5<561,"112",IF(J5<571,"114",IF(J5<581,"116",IF(J5<591,"118",IF(J5<601,"120",IF(J5<611,"122",IF(J5<621,"124",IF(J5<631,"126",IF(J5<641,"128","T"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))). In another sheet named April in n5 cell i have =IF('(1)'!$A$5:$A$20905=APRIL!$A5,(SUMIF('(1)'!$I$5:$I$20905,APRIL!$M$1,'(1)'!$K$5:$K$20905)),0) formula. This always returns 0, whether k5 has anything. Can you help me?

First off, I would replace the ridiculously long IF formula in K5 with this somewhat shorter version. My tests show that both formulas return the same results. =IF(J5<1,0,IF(J5>=641,"T",INT(J5/10)*2+2))

Another option would be a VLOOKUP table which I can explain if you are interested.

As far as the reason why your SUMIF doesn't work, it is probably due to the fact that you have quotes around all of your numbers in the ridiculously long IF formula. By putting quotes around "0", "2", etc. you are telling Excel that those are text strings not numbers.

A text 2 is not equal to a number 2 when comparing them for a SUMIF. Try this:

In A1 enter

="20"

In B1 enter20

In C1 enter=A1=B1You should see FALSE because A1 does not equal B1.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Thanks , please show me vlookup formula you have .there is little difference in formula given by you and long formula there already in.when it is number like 110 or 120 there is difference of 2 in both long one is correct , other wise both are same .

regards

dockhem

message edited by DOCKHEM

My apologies. I posted the first formula I had tried, not the final one. Try this one instead:

=IF(J1<1,0,IF(J1>=641,"T",IF(J1/10=INT(J1/10),INT(J1/10)*2,INT(J1/10)*2+2)))

This one deals with "the tens" (10, 20, 30, etc.) correctly. The earlier one did not.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

thanks i found another short formula too. thank u very much

Would care to share it with us? I'm always looking for ideas based on examples of how others accomplish the same goal.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

why not ,it is IF(J5<1,0,IF(J5>=5000,"T",(INT((J5-1)/10)+1)*2)), and is correct.it helped me a lot.

I have another problem , can u please solve ? i am trying a formula which returns #value! but value is there in the columns . can u tell where is mistake in these formula .=SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$I$5:$I$20905,APRIL!$AI$11)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$E$4)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$E$5)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$E$12)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$G$7)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$G$12)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$G$17)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$G$24)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$E$11)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$A$5:$A$20905,$A$5) and this returns false =IF(('(1)'!$I$5:$I$20905=$AI$11),IF(('(1)'!$A$5:$A$20905=$A$5),SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$E$3)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$E$4)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$E$10)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$E$11)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$G$6)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$G$11)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$G$16)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$G$23)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$G$28)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$G$29)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$G$30)+SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$N$5:$Z$20905,MEDICIN!$G$31))) why ?

Two items: First, your original formula was written to return "T" for values >=641. The formula you posted in Response #6 returns "T" for values >=5000. Why the change?

Second, since the question asked in # 7 is not related to the formula that this thread dealt with, you should post your latest question in it's own thread, with a proper subject line.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks you for reply there is no limit regarding this number 641 or 1000 ,excel did not take more entries after 641 so I stopped there , I do not know about "T" but it worked so I entered it, sorry for my ignorance . this number generally ranges between 0 to 1000 or 1200. but I added 5000 at the extreme .

Second, sorry for this , I did not know .

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History