• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consolidation Report

sdsurzh

Member
Hi,


Can you please download the below and help me out the output sheet with some formula.


Now i am manually entering the data from rawdata sheet.


Even i tried SUMIFS it is not working. Excel Heros please help me.


http://www.2shared.com/file/5GnIZ0tm/Value.html


Thanks in advance,

Suresh Kumar S
 
Enter in Output!B2 and drag down and right


=SUM(OFFSET(Rawdata!$A$1,MATCH(Output!$A2,Rawdata!$A$2:$A$53,0)+COLUMN(A1),,1,4))


Hope that helps!


Regards,

Faseeh
 
Hi prazada82,


Yes why not. We move from inside out:


=SUM(OFFSET(Rawdata!$A$1,MATCH(Output!$A2,Rawdata!$A$2:$A$53,0)+COLUMN(A1),,1,4))


Match() is used to find what we are looking for, Lets say we are looking for "A", this MATCH() will give us a the Index Number of the Match, add 01 to it that will be yield when COULUMN(A1) will be evaluated.


You note that when we will drag this formula to right, the COLUMN() values will change, thus giving series of rows below the MATCH(), eg if it had been in row 4, draging formula to right will give you 5,6,7 as a result.


This result is then feed to OFFSET(), The evaluated formula will look like this:


=SUM(OFFSET(Rawdata!$A$1,4,,1,4))


This Offset() function can be translated into these wording:


"Go the 4th row of the data and refer to a range 01 row high and 04 columns wide"


This in return will give us a range that SUM() will sum up. I suggest you to evaluate this formula step by step and you will learn how it works. Fell free to ask if it is not clear.


@ Suresh: You are welcome!!


Faseeh
 
Hello Faseeh,


Thank you for the explanation. One thing I wanted to ask you is why is the ",," used in the formula? What is the difference if only one "," is used?


I appreciate your help.
 
Hi ,


Whenever a function is used in Excel , it may or may not have parameters ; one example of a function which does not expect parameters is the TODAY() or NOW() function , where you cannot have it as =TODAY(A1) or =NOW(A1).


At the same time , there are other functions which expect parameters ; even a simple function like the IF function has a maximum of 3 parameters where some parameter(s) will be mandatory , while others are optional. If you see the Excel help on any function , the optional parameters will be enclosed in square brackets [....]


What this means is that if you specify an optional parameter , Excel will take it into consideration ; if you do not specify an optional parameter , then Excel will take a default value for this ( or these ) parameter(s).


For example , suppose you use =IF(A1,,) this is a perfectly valid formula though it will not serve any purpose ! What it means is that Excel will check the value of A1 , and whether it is TRUE or FALSE will return the default value of 0.


You can even use =IF(,,) , which will also always return the value 0 ! You can go one step further , and use =IF(,) , which will always return the value FALSE.


However , you cannot use =IF() , since IF expects at least one parameter which is the condition to be checked ; what the commas in the formula =IF(,,) do is that they act as placeholders ; they tell Excel to take the default value for the parameters which are not specified ; since you have used two commas , Excel knows that you have used default values for all the three parameters viz.


the condition to be checked ,


the value if TRUE ,


the value if FALSE


The default value for the condition to be checked is FALSE , the default value for the value if FALSE is 0 , and so the formula returns 0.


If you use =IF(,) , then Excel knows that you have used only two parameters ; since the default value for the condition to be checked is FALSE , and you have not specified the third parameter , Excel returns FALSE , not 0.


If this explanation is very confusing , going through the Excel help for other functions , and trying them out with commas instead of the optional parameters will clear it up.


Narayan
 
Excellent! I dont think I will have to refer Excel Help. Your explanation itself satisfy my need and this is why I always come to chandoo, when I have an excel question.


Thank you!


Prasad
 
Hi Suresh,


Just bypassing the post, and got some loophole in the Data Format & Formula :),Just trying to cover them as mush as possible.


Can you please Enter the below formula in Output!B2.. and check if it acceptable.. :)

[pre]
Code:
=IFERROR(SUM(OFFSET(Rawdata!$A$1,MATCH(Output!$A2,Rawdata!$A$1:$A$54,0)
+MATCH(Output!B$1,INDIRECT("RawData!A" &
MATCH(Output!$A2,Rawdata!$A$1:$A$54,0) & ":A" &
MATCH(Output!$A2,Rawdata!$A$1:$A$54,0)+5),0)-2,,,4)),0)
[/pre]

@ Faseeh

Just giving some extra power to your formula as it was my 400th post :).. by

* If KEY's are not in order.

* If more than one Amount occurred in Same Key & Same Location.

* If Locations (A) are in not order.

* If Some fields are missing


https://dl.dropbox.com/u/78831150/Excel/Value%20-%20Consolidation%20Report%20%28Suresh%20Kumar%29.xlsx


Regards,

Deb
 
Dear Suresh,

You can try this formula in B2 and copy paste other cells

=SUM(INDIRECT("Rawdata!A"&MATCH($A2,Rawdata!$A:$A,0)+COLUMN(Rawdata!A1)):INDIRECT("Rawdata!D"&MATCH($A2,Rawdata!$A:$A,0)+COLUMN(Rawdata!A1)))


Regards,


Muneer
 
Back
Top