asked on
Excel : How to do
Here is what I am trying to achieve,
In sheet 1 column C have value which are month and year (Jan-2015), now in adjacent column D I have a value in $ (15000,00).
In sheet 2 I Need A1 to give me the sum of all value in column D with the adjacent value being (Jan-2015) and so on.
In sheet 1 column C have value which are month and year (Jan-2015), now in adjacent column D I have a value in $ (15000,00).
In sheet 2 I Need A1 to give me the sum of all value in column D with the adjacent value being (Jan-2015) and so on.
Microsoft Excel
Last Comment
Subodh Tiwari (Neeraj)
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Much easier than anticipated, Thanks a lot I didn't know about pivot table but it worked like a charm. A+
As far as the formula is concerned, you may use SUMIF for that purpose.
On Sheet2, list all your unique months in col. A starting from A2, where A1 being the header Month.
In B1, input Total.
Now in B2 use the SUMIF formula like this.....
=SUMIF(Sheet1!<your month range in sheet1 e.g. C2:C100>,A2,Sheet1!<your value range on Sheet1, say D2:D100)
Remember to make both the referenced range symmetrical in size.
On Sheet2, list all your unique months in col. A starting from A2, where A1 being the header Month.
In B1, input Total.
Now in B2 use the SUMIF formula like this.....
=SUMIF(Sheet1!<your month range in sheet1 e.g. C2:C100>,A2,Sheet1!<your value range on Sheet1, say D2:D100)
Remember to make both the referenced range symmetrical in size.
You're welcome Eric!
Glad you found the solution helpful. :)
Glad you found the solution helpful. :)
A Tip:
Convert your data on Sheet1 into a Excel Table by selecting a cell inside the data set and press Ctrl+T and that will convert your data set to an excel table.
Now insert your pivot table based on your table. The advantage of this is, whenever you add new data on Sheet1, just go to Sheet2 where you have inserted the pivot table, right click inside the pivot table and select Refresh, and your pivot table will reflect the new data which you added on Sheet1.
Helpful?
Convert your data on Sheet1 into a Excel Table by selecting a cell inside the data set and press Ctrl+T and that will convert your data set to an excel table.
Now insert your pivot table based on your table. The advantage of this is, whenever you add new data on Sheet1, just go to Sheet2 where you have inserted the pivot table, right click inside the pivot table and select Refresh, and your pivot table will reflect the new data which you added on Sheet1.
Helpful?