Solved problem in sumifs formula with multiple criteria

March 15, 2014 at 20:42:13
Specs: Windows 7
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 ?


See More: problem in sumifs formula with multiple criteria

Report •


✔ Best Answer
March 17, 2014 at 09:46:05
Assuming Excel 2007 or later, click on Formulas at the top of the screen to open the Formulas ribbon. About 3/4's of the way across the ribbon you'll see the "Formula Auditing" section. One of the most helpful choices in the section is Evaluate Formula.

With that feature you can single step through any formula and watch as each term is evaluated. That is how I was quickly able to determine that the first term in your SUMIFS formula was wrong. As soon as I clicked Evaluate, the #VALUE error appeared where the first term used to be. At that point, I knew where the error was.

Try this simple example:

In a blank worksheet, enter this formula in A1:

=IF(B1=C1,5/B1,10)

You will get a #DIV/0 error, so we'll use the Formula Evaluator to find out why.

With A1 selected, open the Formulas ribbon and click on Evaluate Formula.
Click the Evaluate button slowly, while watching the formula in the dialog box.

You should see each step of the formula being evaluated until the #DIV/0 error appears and you will then know which term caused the error and why.

Once you see how the Formula Evaluator works, you can try it on your long IF formula, watching each step until you see what is causing it to return an unexpected result. Sometimes you will have to go back and forth between the data in your spreadsheet and the Evaluator to try and figure out why the Evaluator returned what it did for any given step, but eventually you should be able to narrow it down to the root cause.

Good luck!

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



#1
March 16, 2014 at 08:13:15
The most common reason for a #VALUE error is that one of your arguments is a TEXT string.

If you are using Dates,
make sure they are really DATES
not a Text String that looks like a date.

A quick way to check:
Select the Date Cell,
right click and select Format Cell,
and change the format to Number.

If the cell remains a Date
than you have a Text String that looks like a Date.

MIKE

http://www.skeptic.com/


Report •

#2
March 16, 2014 at 13:06:19
First, let's look at the syntax for a SUMIFS formula:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,criteria2], …)

Now let's look at one of the rules for the SUMIF function found at:

http://office.microsoft.com/en-us/e...

Unlike the range and criteria arguments in the SUMIF function, in the SUMIFS function, each criteria_range argument must contain the same number of rows and columns as the sum_range argument.

Now let's look at the first term of your SUMIFS formula:

=SUMIFS('(1)'!$O$5:$AA$20905,'(1)'!$I$5:$I$20905,April!$AI$11)

Your sum_range argument contains 13 columns. Your criteria_range1 argument contains 1 column.

BOOM! #Value

I don't have to test your second formula right now.

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


Report •

#3
March 17, 2014 at 04:16:25
Dockhem,

Are you familiar with the formula evaluator feature in Excel?

It is very useful for figuring out what us going within your fomulas because it will allow you to single step through each part of the formula. Had you single stepped through your SUMIFS formula, you would have seen the #VALUE error show up in the first term.

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


Report •

Related Solutions

#4
March 17, 2014 at 05:03:46
actually i am a layman in ms office . i learned every thing by hit and try method . no body taught me anything .i am making excel sheet to calculate data during a month. i enter data daily basis . previously i made a work book that is having entry field very big, i am trying to shorten it with the help of formulas.i am not very much familiar with formula. excel itself help me and few times you people on net.i found one formula on net and now it is done. i found i entered wrong cell reference entry in my formula .

yes i tried single formula also.if formula gave me false in cell , i could not solve that .

thanks dear

regards


Report •

#5
March 17, 2014 at 09:46:05
✔ Best Answer
Assuming Excel 2007 or later, click on Formulas at the top of the screen to open the Formulas ribbon. About 3/4's of the way across the ribbon you'll see the "Formula Auditing" section. One of the most helpful choices in the section is Evaluate Formula.

With that feature you can single step through any formula and watch as each term is evaluated. That is how I was quickly able to determine that the first term in your SUMIFS formula was wrong. As soon as I clicked Evaluate, the #VALUE error appeared where the first term used to be. At that point, I knew where the error was.

Try this simple example:

In a blank worksheet, enter this formula in A1:

=IF(B1=C1,5/B1,10)

You will get a #DIV/0 error, so we'll use the Formula Evaluator to find out why.

With A1 selected, open the Formulas ribbon and click on Evaluate Formula.
Click the Evaluate button slowly, while watching the formula in the dialog box.

You should see each step of the formula being evaluated until the #DIV/0 error appears and you will then know which term caused the error and why.

Once you see how the Formula Evaluator works, you can try it on your long IF formula, watching each step until you see what is causing it to return an unexpected result. Sometimes you will have to go back and forth between the data in your spreadsheet and the Evaluator to try and figure out why the Evaluator returned what it did for any given step, but eventually you should be able to narrow it down to the root cause.

Good luck!

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


Report •

#6
March 17, 2014 at 10:23:47
yes it shows where defect situated,one more thing , how to add many formulas in to one , is there any particular criteria ?
thanks
regard

Report •

#7
March 17, 2014 at 10:45:35
I have no idea what you are asking for in your latest post. You need to be more specific.

In addition, if that question is not related to the formulas in this thread, please start a new thread with a relevant subject line.

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


Report •


Ask Question