+ Reply to Thread
Results 1 to 4 of 4

Sumproduct formula "breaks" when referencing too many cells, why?

Hybrid View

  1. #1
    Registered User
    Join Date
    12-05-2005
    Posts
    6
    Thanks for the reply.
    I am worried my formula is a little too complicated, but here is what I am trying to do:

    About A2:A4000, to provide a little more detail, this range actually contains a combination of a region and product family code, so the cells would contain: US P214A, US P214, Japan P42, Japan P123, US P214, Europe P42, etc.

    Keys here are:
    1. all the parts for each region aren't adjacent (nor are the parts),
    2. the text in column A can be of any length
    3. multiple cells in column A may contain the same information (like US P214 above), and I need to sum the units between the dates for both of those rows. sadly I can't reformat to make things easier...
    4. I also need to differentiate between US P214 and US P214A, for example, but I haven't quite figured out how to do that part in my formula yet...

  2. #2
    pinmaster
    Guest
    Not sure if this will help but assuming every cell in A2:A4000 has data and that data consist on a region followed by a code with a space in between then maybe:

    =SUMPRODUCT((RIGHT('the data'!A2:A4000,LEN('the data'!A2:A4000)-FIND(" ",'the data'!A2:A4000))=A1)*('the data'!B1:HJ1>=A3)*('the data'!B1:HJ1<=A4),'the data'!B2:HJ4000)

    A1 = code like P42, P123, P124 ...etc
    A3 = start date
    A4 = end date

    but as I said, it will only work if all cells contains 1 space between the region and code, if only 1 cell doesn't have a space it or is empty then it will error out!

    If that doesn't work then you might think of braking it down by region and then adding them all together. Put your regions in a column then in the second column type: =$A$1 copy down then use something like

    =SUMPRODUCT(('the data'!$A$2:$A$4000=B1&" "&C1)*(......
    where B1 is the region and C1 is the code

    HTH
    Jean-Guy

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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