Dear expert
I have week end sales report i want find total sales by discount for day1 4% ,day2 5% after two days all are 15% only for Pencil and Pen product, rest of all no discount
find the attachment
Dear expert
I have week end sales report i want find total sales by discount for day1 4% ,day2 5% after two days all are 15% only for Pencil and Pen product, rest of all no discount
find the attachment
Like This?
Data Range
L 2 TOTAL SALES WITH COMMISSION 3 =H3*0.04+F3*0.05+0.15*(D3+B3) 4 =H4*0.04+F4*0.05+0.15*(D4+B4) 5 =SUM(A5:H5) 6 =SUM(A6:H6) 7 8 9 TOTAL SALES WITH COMMISSION 10 =J10*0.04+H10*0.05+0.15*(F10+D10+B10) 11 =J11*0.04+H11*0.05+0.15*(F11+D11+B11) 12 =SUM(A12:J12) 13 =SUM(A13:J13)
Last edited by xladept; 07-31-2016 at 04:11 PM.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
formula what you suggest Its Kind of manually reference selection. I want fill down if formula with criteria conditions for Pencil and pen becoz i have data till 20000 rows i cant do select the range manually
If you restructure your data into a table with dates down the side and products across the top, your calcs will be much simpler.
The way you have your data structures makes creating a copy-able formula very complex
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Or you may try something like this.....
In L5
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
OK sir Please Restructure data into table with dates down as your convenience, make it simple. i dont know how to do restructure.
Forgot to include the condition for Rubber and Ink.
Please try in L5
and copy down.Please Login or Register to view this content.
Please find the attached and on New Layout Sheet, you will find that the data is organized in a tabular form in col. A:D and formatted as an Excel Table.
So you may keep on adding data down the rows.
You will also find two pivot tables next to the source data table to get the visual information from the source data. Keep one which suits your requirement.
Once you add more data in the source table, right click on any cell in the pivot table and choose Refresh to update the pivot table.
This layout is easy to maintain. See if that helps.
@ sktneer It works from formula what you gave above!! Thanks alot
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks