How can you look up how much each category has made into the category column using a formula?
Can a formula identify the category within the item number?
Example:
item number: GW14SO0000003
item category: GW14SO
item counter: 0000003
mockup.xlsx
How can you look up how much each category has made into the category column using a formula?
Can a formula identify the category within the item number?
Example:
item number: GW14SO0000003
item category: GW14SO
item counter: 0000003
mockup.xlsx
with a pivottable you don't need formulas and is very simple.
I dont think I can use a pivot table for this. Did you open the mock up file?
with little effort (in excel2003)
How did you get column I to only select the categories?
Is this method automated? When I add new rows of information, the pivot table does not update automatically?
you have to update the pivottable manually or you can write a macro to do so.
What were the steps you used to create the pivot table? I cant seem to get it right
You can do it with formulas
Use this in C29 of your mock-up file to get unique categories
=INDEX(LEFT($D$6:$D$25,6),MATCH(0,INDEX(COUNTIF($C$28:C28,LEFT($D$6:$D$25,6)),,),))
and drag formula down.
And this one for calculations in D29
=SUMPRODUCT((LEFT($D$6:$D$25,6)=$C29)*(F$6:F$25))
then down and across
C D E F 28Category Total Purchases Sold Profit 29GW30BF $5.00 $43.00 $48.00 30GW37VS $4.00 $26.00 $28.00 31GW14SO $1.00 $7.00 $8.00 32GW67TZ $7.00 $93.00 $98.00 33GW34PD $3.00 $69.00 $72.00
Last edited by AlKey; 12-29-2013 at 10:33 AM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Wow, life saver. I was up all last night trying to figure this out. I was trying to use If ISerror Search, but had not luck combing formulas, maybe I was going the whole wrong way about the arguments.
Where in the formula does it tell you to identify the category, example: GW30BF
=INDEX(LEFT($D$6:$D$25,6),MATCH(0,INDEX(COUNTIF($C$28:C28,LEFT($D$6:$D$25,6)),,),))
This formula extracts first 6 characters from the unique list (LEFT($D$6:$D$25,6)
The sumproduct first part is comparing it and uses as a criteria.
Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
How can I add monthy sales to the new table created in the revised mockup workbook? thanks
mockup.xlsx
Last edited by matt323; 12-29-2013 at 05:07 PM.
What date should be use Sell Date?
In H29 across and down
=SUMPRODUCT((LEFT($D$6:$D$25,6)=$G29)*(TEXT($C$6:$C$25,"mmmm")=H$28)*($G$6:$G$25))
This is more of a personal preference, how can I tell the formula to avoid adding the symbol "-" in the cells to avoid getting a #value! as a result. I revised the workbook below.
mockup_alkey.xlsx
This will replace "-" with 0
For the Total Purchases
=SUMPRODUCT((LEFT($D$6:$D$25,6)=$B29)*(SUBSTITUTE(F$6:F$25,"-",0)))
For the Monthly
=SUMPRODUCT((LEFT($D$6:$D$25,6)=$G29)*(TEXT($C$6:$C$25,"mmmm")=H$28)*(SUBSTITUTE($G$6:$G$25,"-",0)))
Awesome, now just 1 more question. How would I add weekly sales in the weekly sales section in the revised mockup workbook?
mockup_alkey1.xlsx
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks