Hello,
The file contains a large amount of data, including multiple categories of daily banking data. I tried to sum the Date column per date, including the criteria by Actual / Project in Row 2 and Cat in Column A
I am having trouble summing data from the Detail to the Summary tab. I tried using SumProduct and Sumifs with Index & Match, but neither of them worked.
criteria:
1 - 3rdP ( in column A)
2 - Actual / Project ( Row2 )
3 - Date (Row3) - the return value based on the Date range
Please help me determine what is the best formula to work on these criteria.
Here is the Daily data:
Please try to upload the file rather than paste text from it. And please show the expected results.
Hi Riny,
Thank you so much for your help.
Thanks for the file, but now please explain what you want to achieve. I don't understand the design of the file, to be honest. It has two #REF! errors in the detail sheet and . The summary sheet duplicates the same week in G:G and I:M whereas the detail sheet spans two weeks.
Why are the opening balances for one day not equal to the closing balances of the previous day?
And it would be helpful if you could indicate what the results should be for rows 22:26 in the summary sheet if you would calculate it manually.
I would argue for a change of how you add your raw data, if you have the data in a tabular formatted table then it is easy to get the data you want. See attached modified copy of your file.
Br,
Anders
Hi Riny,
Thank you for taking the time to review the file. I've attached the updated file-v1
.
**Detail Tab**: The detail sheet is manually populated with banking data, and the formulas are in place to sum the subtotal row..
**Summary Tab**: I apologize for the confusion; I made an error on the Summary tab. The dates should accurately reflect the details, covering Monday to Friday.
**Beginning Balance**: I'm sorry about that oversight; it didn't update after I copied and pasted the information.
**Goal**: The objective is to calculate the total for the "Cat" column per date column, using the criteria of Actual vs. Project. I hope I have explained it clearly.
For example:
- **3rdP in Detail**: Rows 10-12 and 23, 25, 26-31
- **CSH**: Rows 21-22
- **CC**: Rows 25 and 32
Feel free to let me know if you need any more clarification.
Best regards,
Thank you for checking the file. The detailed data from the bank activities was manually updated daily. It is not what they want to see.
Thank you so much.
Really sorry, but I still don't understand. Can you manually fill in some numbers in the Prior Week Comparison?
I entered the Index Match formula on the "Prior Week Comparison" in Row 22, but the formula is not dynamic; it is added per Row. The file is big, and it is very time-consuming to add it per row.
How do I sum up the Date column?
Thank you
Hi again. The attached file includes a formula that might work for you. I entered it in P22 and copied it to the right. does that help?
The data in a tabular format is to be able to easily work with the data, to present the data in a good way you of course need a better view, for example the pivot like structure you have, but such view can easily be built from such table.
Br,
Anders
Ooops. Something must have gone wrong. Trying again.
