Results 1 to 7 of 7

Thread: Organize item sales by date and other variables based on cell content

  1. #1

    Organize item sales by date and other variables based on cell content



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm trying to work on this spreadsheet and come up with a formula to calculate sales based on item SKU and the month in which they were sold, so one can see month to month the sales of a given color. I'm using this formula

    =SUMIFS('SALES DATA'!$B$2:$B$9999,'SALES DATA'!$D$2:$D$9999,"*"&$A3&"*",'SALES DATA'!$A$2:$A$9999,"="&B$1)

    Range B2:B9999 on the Sales Data sheet is the number of items sold of that color during an order (if someone bought one, it displays one, if someone bought 2 it displays 2 etc), range D29999 on Sales Data is the item sku on the order, A3 is the item color to compare it to within the sku on the other sheet. Range A2:A9999 on Sales Data is the date of the order, and B1 is the month to compare it to. I've pretty much traced the problem to the way the date is displayed or processed. I can get some results if in row 1 I type the months as 1/1/15, 2/1/15 but obviously that will only return results from that day, not the whole month. Typing the whole month manually will not work as all the dates in the Sales Data sheet are returned in a format of mm/dd/yyyy and cannot be changed (this is how our shipping application outputs reports). Any way I can compare these dissimilar dates and not totally jack up the formula? Is there an easier way I could be sorting this? I'll be checking back frequently to answer any questions or provide any clarification. Enclosed is a pared down version of the workbook with only essential pages that are tied to this sheet by formula so the formula will work accurately when applied to the full book.
    Attached Files Attached Files

  2. #2
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    114
    Articles
    0
    Excel Version
    365
    Hi,
    a simple solution:
    --> add a new column which calculate the month (number)
    --> the use this formula =IF(ISNONTEXT($A3);0;SUMIFS('SALES DATA'!$B$2:$B$9984;'SALES DATA'!$D$2:$D$9984;"*"&$A3&"*";'SALES DATA'!$E$2:$E$9984;MONTH(B$1)))
    --> the total that this formula returns is 700, different from the real total 742. The reason, is the homogeneity of your data into the sheet "SALES DATA". Fro Excel, "Olive-Drab" and "Olive Drab" are different.

    Hope this solution will help you.
    Good luck
    CA4000 JUNE 1 EXPERIMENTAL basic 3.xlsm

  3. #3
    Try my solution, Use tables to better understand what each formula is doing, add two calculated columns for year and month

    see attached workbookNew CA4000 JUNE 1 EXPERIMENTAL basic 32.xlsm

  4. #4
    ^this has worked perfectly, thank you so much. It's such a complex issue that it's nearly impossible to google properly, so I definitely appreciate the assistance. Unfortunately that's only half the battle but a major hurdle has been cleared at the very least. I also have to expand this to incorporate size as a variable as well for products with multiple sizes. Would I be able to just add another wildcard variable? if so, what would be the proper syntax? I applied the current formula to this workbook and have been toying with a few different things but nothing seems to work. Is this impossible with the workbooks layout or can this be done?


    CA5100 JULY 1 experimental basic.xlsm

  5. #5
    Hi

    your attachment does not seem to be an excel workbook

  6. #6
    How so? it downloads and opens just fine for me

  7. #7
    Saved and uploaded again, hopefully this works for everyone
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •