SUMIF returns 0 value though there is value 2,or more than 2

March 9, 2014 at 08:35:30
Specs: Windows 7
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?


See More: SUMIF returns 0 value though there is value 2,or more than 2

Report •

#1
March 9, 2014 at 12:28:46
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 enter 20
In C1 enter =A1=B1

You 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


Report •

#2
March 12, 2014 at 20:00:41
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


Report •

#3
March 13, 2014 at 07:13:22
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


Report •

Related Solutions

#4
March 13, 2014 at 10:30:02
thanks i found another short formula too. thank u very much

Report •

#5
March 13, 2014 at 11:32:04
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.


Report •

#6
March 15, 2014 at 08:30:53
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.

Report •

#7
March 15, 2014 at 09:25:36
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 ?


Report •

#8
March 15, 2014 at 20:23:14
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.


Report •

#9
March 15, 2014 at 20:39:15
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 .


Report •

Ask Question