Calculating weekly totals with a variable number of items per week

[Ben466]

Hi, first post and hopefully it's in the correct sub-forum - apologies if not

I'm trying to create a ledger sheet for a company I do consultation work for that matches their existing ledger sheet, hence the layout in the screenshot below

Pretty simple sum formulas at the top of each column under the dark green headings but a desirable function of the sheet as it's filled out throughout the year is to have a weekly total

Each row entry represents a payment into the business. Unfortunately, there will be a different number of payments every week.

I've thought that this could be implemented by having a wk no. column and for every payment in, as long as the week number that the payment was made was filled in, that could be used to sum the various payment values for that week number.

As per the screen you can see I've thought about using a drop down in the cell titled Sel. Week, and directly below this once the week is selected, the total for that week would be displayed.

I'm by no means a regular Excel user though so I thought I'd ask if anyone could give me some pointer steps towards achieving this

Any help is appreciated, thanks

Capture.PNG

[AliGW]

Welcome to the forum!

Are you really looking for a VBA solution, or something using formulae?

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Ben466]

Thank you,

So as regards VBA or formula; I guess I'm trying to keep it as simple as possible so formula as a preference but I assumed for this VBA would be required.

I've attached the file I'm working on, all sample data is representative as far as is required and I've shown how I would like it to display as requested

Hopefully this should be clearer now

Thanks,

[JeteMc]

Here is an example of how you might set up what you want.
A list of week numbers populates XFD2:XFD53
Cell B2 is populated using data validation with the list as the source
Cell B3 is populated using: =SUMPRODUCT((A8:A30=B3)*(D8:H30))
Let us know if you have any questions.