Auto sum data across multiple sheets based on 2 criteria

[jakmc25]

So i need help with an excel document I've been working on, I've been stuck on this for awhile now. I have 2 things i'm trying to automate but i'm not sure if its possible.

Basically this is used to track income based on client name, type of payment method, ect..

1. What i'm trying to do is on the "Year to Date" sheet i have a client list that i manually update with new clients as i get them.
Q: Is there a way that when a clients name is typed into one of the monthly sheets it will automatically reference this list on the "year to date" sheet and if the name isn't found it will add the name to the list?

2. On the monthly sheets i put how much each client has paid during that visit and also how much they have tipped (some clients come multiple times a month).
Q: On the "Year to Date" sheet i would like to sum up the amount paid and tipped throughout the year based on the clients first and last name.

Any help with this would be greatly!

 

[mrhstn]

Hi and welcome to the forum

It sounds like you want to sum across multiple sheets, a 3D SUMIF?

Mike Girvan explains it well based on a similar question from the board. - https://www.youtube.com/watch?v=tmh5vI5dK6o

 

[jakmc25]

Thanks for the reply. But i don't think this function will work in this case. On my summary page i have a list of clients names, next to there names i want to show the amount of money they have spent all year, this way i can quickly compare them.

 

[mrhstn]

I created an example for you, first 6 months not the full year (just cos 6 months is plenty to get the idea)

January to June have entries with names and cost type, service or charge. In Master there is a sum across the sheets for the entire period.

https://app.box.com/s/7xbrq3gzvmegeg0gc1ft9tt4icip5r12