+ Reply to Thread
Results 1 to 7 of 7

Add an additional variable to formula for calculating sales per month based on color/size

  1. #1
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel:Mac 2011 14.4.1
    Posts
    25

    Add an additional variable to formula for calculating sales per month based on color/size

    I hope my title makes sense as well as the description of my issue as I feel it's rather esoteric so please bear with me.

    I had needed to work out the formula to calculate sales per month based on color name within a sku a few weeks ago, and with some help I was finally able to get it. The formula I am currently using for that is

    =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)))


    However, since the products we sell are clothing, I need to somehow add another variable to this formula so it will not only detect color but size as well and place that in the correct slot, in the correct month. I know the issue sounds sort of convoluted, but all of the major hurdles have been cleared (i feel) and I just need to know how to further pare down my results. I will attach the workbook so you can fully understand.

    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?
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel:Mac 2011 14.4.1
    Posts
    25

    Re: Add an additional variable to formula for calculating sales per month based on color/s

    Here is the formula used in a single size workbook to see how the totals are calculated in action
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Add an additional variable to formula for calculating sales per month based on color/s

    Try this ...

    =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(DATEVALUE(G$1&"1")),'SALES DATA'!$D$2:$D$9984,"*"&$A4&"*"))

    Note change in RED which changes the literal month (June) to number which you need to compare with column E of SALES DATA.

    The Additional wild card will work PROVIDED that none of the "sizes" appear in the other text of the SKU i.e if "S" appeared as part of the description but real size was "M" you will get erroneous counts.

  4. #4
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel:Mac 2011 14.4.1
    Posts
    25

    Re: Add an additional variable to formula for calculating sales per month based on color/s

    Unfortunately that has not worked either. I tried to make the sizes more specific by putting a space in front of them, so the formula should be looking for " S" " M" etc which is how the skus are written, however I come up with no totals when I apply your formula to the workbook

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Add an additional variable to formula for calculating sales per month based on color/s

    Yes it does! The formula was referencing the wrong column for January so everything was out of step.

    See attached ....
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Add an additional variable to formula for calculating sales per month based on color/s

    First, On Monthly Sales, I converted your Months on top to numbers. B1 = 1/1/15, C1 copied right = EOMONTH(B1,1). Cells formatted to Custom mmm

    I decided it was too complex for SUMIFS and moved to SUMPRODUCT.
    In B4 copied right and down

    =IF(ISNONTEXT($A$3),0,SUMPRODUCT('SALES DATA'!$B$2:$B$9984,--(ISNUMBER(SEARCH($A$3,'SALES DATA'!$D$2:$D$9984))), --(MONTH('SALES DATA'!$A$2:$A$9984)=MONTH(B$1)), --(RIGHT('SALES DATA'!$D$2:$D$9984, LEN($A4))=$A4)))

    Note: With SUMPRODUCT, you also don't need Column E on 'Sales Data'. You can extract month straight from Column A
    You will need to manually change $A$3 in the above formula (2 places) when copying down to other Colors
    See attachment
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Add an additional variable to formula for calculating sales per month based on color/s

    just note in ChemistB formula the NONTEXT references are absolute so you cannot simply copy the formula from one "product" to the next. Just a reminder as your product list increases.

    I agree entirely with his comments re dates (as I changed them to date format in a previous posting but there seems at some point to have been a reversion to text months).

    And using RIGHT is the answer to problem of using wild cards.

    Overall a much better solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculating sales trend based on formula for a date
    By PCLynx in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-17-2014, 11:49 PM
  2. Formula to sum sales over 12 month period not based on calendar year
    By cymraeg in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-02-2014, 12:02 PM
  3. Replies: 6
    Last Post: 10-19-2013, 04:53 PM
  4. [SOLVED] Calculating sales with respect to item and in a particular month
    By peaceracer in forum Excel General
    Replies: 12
    Last Post: 07-19-2012, 03:23 AM
  5. Replies: 2
    Last Post: 03-15-2012, 12:11 AM
  6. [SOLVED] SUM a range of sales based on month
    By Manos in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-08-2006, 05:35 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1