Forum Discussion
My first post, best regards to all my seniors here.
Hello,
I am Anindya Banerjee, by qualification a Civil Engineer. Working under Panchayat and Rural Development, Govt. of West Bengal, India. I need to send some reports based on data available under our Govt. website. I am new to excel but I think I have basic knowledge in excel.
At present I am including a table, where I need to calculate the sum of total amount expended under a particular Work Name. Please take a look into the table which is available at our Govt. Website. Please advise me how to calculate the Sum of Expenditure under a particular work name.
I only need two fields, 1) Work Name and 2) Total Expenditure.
I am not being able to understand how to calculate this. Please help me.
Thanks in advance.
Anindyanuri
FIrst of all, although you think you have a table, that data on your Excelview tab is in fact not laid out as an Excel Table, and that interferes with a clean way to do what you're asking. A table would not have the many rows you have breaking one Work Project apart from the others. They could all be together, using a column (as I've given you in column A) to differentiate the project to which the row refers.
I've taken the liberty on the attached to modify the first three projects into a tabular format, but you should do what I've done to all of them.
I've then created a tab called "Bus Table"--meaning "Business Table"--where you can store a table (as a Table, it should have only rows and columns that are consistent, no intervening "pretty" tables in a different color and layout); the table I've put there enables a VLOOKUP formula on the "Query" tab to take a Work Project Code and find the name of the project. It also then enables a SUMIF function to total the costs associated with that project.
There's a LOT more that could be done to make this work very smoothly, but that would work only if you have the most recent version of Excel. This would take advantage of the Dynamic Array Functions like UNIQUE, SORT, FILTER. See if you can enter those as functions in your spreadsheet. If so, once you've made all the projects into the table format, come back and I or someone else can show you how to make it truly a dynamic and lively business workbook.
But your first step is to make that ExcelView sheet into what is truly an Excel Table. Make it functional, not pretty. Excel has the ability (see FILTER above) to produce project specific listings such as you've been creating, as Output.......but it needs to work from what is truly an Input table.
6 Replies
anindyanuri , As a variation to the formula solution mathetes has provided, here a Power Query solution. I understand you are a beginner in Excel and this may be a bit more advanced but the problem you are trying to solve requires these types of solutions.
Sir,
What you have done is my requirement but I don't know how to do that. I feel I need to learn more about Excel. I am trying that first.
Thank you very much.
anindyanuri , Glad that the solution is what you are looking for. I would recommend learning the basics of Power Query (there are plenty of great tutorials on YouTube). The returns are high on a relatively small investment of time. Also I agree with mathetes recommendation to get unformatted CSV output if available. Formatted Excel reports are a beast to work with.
FIrst of all, although you think you have a table, that data on your Excelview tab is in fact not laid out as an Excel Table, and that interferes with a clean way to do what you're asking. A table would not have the many rows you have breaking one Work Project apart from the others. They could all be together, using a column (as I've given you in column A) to differentiate the project to which the row refers.
I've taken the liberty on the attached to modify the first three projects into a tabular format, but you should do what I've done to all of them.
I've then created a tab called "Bus Table"--meaning "Business Table"--where you can store a table (as a Table, it should have only rows and columns that are consistent, no intervening "pretty" tables in a different color and layout); the table I've put there enables a VLOOKUP formula on the "Query" tab to take a Work Project Code and find the name of the project. It also then enables a SUMIF function to total the costs associated with that project.
There's a LOT more that could be done to make this work very smoothly, but that would work only if you have the most recent version of Excel. This would take advantage of the Dynamic Array Functions like UNIQUE, SORT, FILTER. See if you can enter those as functions in your spreadsheet. If so, once you've made all the projects into the table format, come back and I or someone else can show you how to make it truly a dynamic and lively business workbook.
But your first step is to make that ExcelView sheet into what is truly an Excel Table. Make it functional, not pretty. Excel has the ability (see FILTER above) to produce project specific listings such as you've been creating, as Output.......but it needs to work from what is truly an Input table.
Sir,
First thing first. You have found it correctly, that is not an Excel sheet. That is a database maintained by our Govt.. When I fetch the data, that gives me an option to download the data in Excel format. What I have given is the same downloaded copy in Excel format.
From your reply I merely understand what you have done. That's purely my problem. I need to learn more about the VLOOKUP and other functions you have used in the table. I am trying to learn those basics first.
One thing I can say that, if I put =UNIQUE, =FILTER, =SORT in my function bar, it does not return anything. I am using Office 2019. What I can say that, in my DATA ribbon I have the option DATA and FILTER option.
However, I really like to offer my best gratitude to you for your quick response. With love from India.
Anindyanuri
You wrote: First thing first. You have found it correctly, that is not an Excel sheet. That is a database maintained by our Govt.. When I fetch the data, that gives me an option to download the data in Excel format. What I have given is the same downloaded copy in Excel format.
It is Excel, but not a fully functional Excel Table. Let me ask you, since that's an option they offer--to download as in an Excel format. Do they also offer a CSV (comma separated values) format? If so, try that. It might be closer to a standard Excel table, once imported.
As background to that suggestion, there's a major global credit card company (American Express) that gives an option for Excel format or CSV. I've found to my dismay that their Excel is all prettied up and, as a result, actually far harder to use in Excel for anything other than viewing. It works far better if you're actually planning to extract data from it (as you're wanting to do here) to download it as CSV and import that into Excel. It's closer to "raw data" that way.
And sending love back to you in India. You might find this hard to believe, but I was born in India (the city of Guntur, in southeast India) nearly 79 years ago (1941). Haven't been to India since the 1950s, and lived more of my early years in Japan....but it is the country of my birth. Many of my first words were in Telegu.
