I have multiple worksheets that track 12 months revenue for each shop. One customer is assigned to each of the month.
I would need to have a consolidated 12 month revenue for each of the customer in a separate sheet. In the attachment, I am using If function to sum, work fine for 3 shops and 4 customers. But the actual model will have 35 shops and 25 customers. There must be an easier way to do this.
Appreciate any suggestion..
define a name "Shops" with all shops
Note the comma's semi-colons
Please Login or Register to view this content.
Looking for great solutions but hate waiting?
Seach this Forum through Google
www.Google.com (e.g. +multiple +IF site:excelforum.com/excel-general/ )
www.Google.com (e.g. +fill +combobox site:excelforum.com/excel-programming/ )
Ave,
Ricardo
I modified your sheet. Easier. Look at the names List for "Shops"
Thanks rwgrietveld for the enlightenment.
The formula works fine in the cell you modified.
Now a new problem, how do I copy the formula to other cells since the Reference cell are entered as text in order for the INDIRECT function to work?
=SUMPRODUCT(COUNTIF(INDIRECT("'"&Shops&"'!$D$3"),RIGHT(B2,3)),SUMIF(INDIRECT("'"&Shops&"'!$D$4"),">0"))Please Login or Register to view this content.Please Login or Register to view this content.
Dear HamnEgg,
To be honest with you, I think your worksheet are not ordered very properly. Please try to find a way that all data is more logical grouped and you can use this function, maybe with an offset in it.
You are absolutely right.
The template is originally use to plan revenue by shop only. I am exploring a way to allocate the revenue by customer too.
Anyway, thanks you assistance and advice.
Really appreciate.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks