Please point me in the right direction.

[Nygie]

Hi everyone.

I have a simple table that has expenses in.... Date, Description, amount and ExpenseType.
On a separate tab I have a summary table to give a total of each expense type using the Sumif function.

Sometimes we get data that is out of the range we are working on and then we split it out and do a summary for the split data. What I am looking to do is formulate the summary table (possibly need to be done with VBA, not sure) so that it only totals the visible records in the expenses table, so that we can simply choose that data range we are using and the summary reflect this.

Another possible way would be to have two date ranges on the summary tab and use these as comparative data criteria.
I'm not really sure the best way to start looking at this.

I'm kinda thinking whilst I type and another way would be to have the main data table, then a second data table that pulls records through based on date criteria, (although I have had issues with table size being either too big or too small for the data) then summarise the second table.

Sorry for waffling.
Any pointers would be appreciated.
Thanks

 

[hiker95]

Nygie,

It would help if we could see your actual raw data workbook/worksheets. And, can we see what the results (manually formatted by you) should look like?

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[Nygie]

Nygie,

It would help if we could see your actual raw data workbook/worksheets. And, can we see what the results (manually formatted by you) should look like?

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

Something went wrong with the Dropbox install but I have the Onedrive link..... https://1drv.ms/x/s!AivYfagw9jkqjG04DprCiiRx3C3q
Thanks.

 

[Nygie]

DB working now https://www.dropbox.com/s/psllgu9z165yxbh/Test Data.xlsx?dl=0

 

[hiker95]

Nygie,

Thanks for the new workbook.

I do not understand what you are trying to do.

Maybe someone else on MrExcel will be able to help you.

 

[Nygie]

Simply put, when I restrict the data table by certain dates, I would like the summary table to reflect the total, only what is on display on the data table.

 

[Nygie]

I went with the start date and end date cells and used the sumifs function. Would have been nice to filter the data itself and the summary update automatically as sometimes the data will need printing along with the summary. Not a big job but every little helps.

 

[mopp1]

You can use =aggregate(9,5, array) to sum visible rows only.

 

[Nygie]

I'll give it a try when I get a second, thanks.