Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151

    Question Unanswered: Sumif Limitation problem

    I have this formula below but it seems that it will only let me add 17 SUMIF functions. I do one sumif per sheet i have 19 sheets i i can only do 17 what is the way around this problem ? here is my formula..

    =SUMIF('Sobeys West 242'!$F$9:$F$400,$E9,'Sobeys West 242'!Q$9:Q$400)+SUMIF('West Fair 242'!$F$9:$F$400,$E9,'West Fair 242'!Q$9:Q$400)+SUMIF('All Others 243'!$F$9:$F$400,$E9,'All Others 243'!Q$9:Q$400)+SUMIF('Sysco 243'!$F$9:$F$400,$E9,'Sysco 243'!Q$9:Q$400)+SUMIF('GFS 243'!$F$9:$F$400,$E9,'GFS 243'!Q$9:Q$400)+SUMIF('244 Riv Major'!$F$9:$F$400,$E9,'244 Riv Major'!Q$9:Q$400)+SUMIF('BCI 247'!$F$9:$F$400,$E9,'BCI 247'!Q$9:Q$400)+SUMIF('Buy Low 247'!$F$9:$F$400,$E9,'Buy Low 247'!Q$9:Q$400)+SUMIF('FCL 247'!$F$9:$F$400,$E9,'FCL 247'!Q$9:Q$400)+SUMIF('GPI 247'!$F$9:$F$400,$E9,'GPI 247'!Q$9:Q$400)+SUMIF('HYL 247'!$F$9:$F$400,$E9,'HYL 247'!Q$9:Q$400)+SUMIF('PI 247'!$F$9:$F$400,$E9,'PI 247'!Q$9:Q$400)+SUMIF('Safeway 247'!$F$9:$F$400,$E9,'Safeway 247'!Q$9:Q$400)+SUMIF('Sobeys 247'!$F$9:$F$400,$E9,'Sobeys 247'!Q$9:Q$400)+SUMIF('W.Grocers 247'!$F$9:$F$400,$E9,'W.Grocers 247'!Q$9:Q$400)+SUMIF('Walmart Ontario 247'!$F$9:$F$400,$E9,'Walmart Ontario 247'!Q$9:Q$400)

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could write a vba function to test which is null, then pass that to the SUMIF() function.

    On the "out of left field" tip... if you have 19 sheets of similar data, perhaps you should consider using a different platform entirely.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    sorry iam not to good with VB how would i do that

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Wait... you're only supplying two arguments to SUMIF() and it wants three... How does that work?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    Sorry but thats 3 arguments SUMIF('Sobeys West 242'!$F$9:$F$400,$E9,'Sobeys West 242'!Q$9:Q$400)

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ah, I see now, i didn't see the $E9. I suppose you could break the formula up into two cells if you don't want to tinker with VBA. Simply cut the thing and half into two cells, then add the two cells together.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    The reason for the limit is this:
    Code:
    Length of formula contents: 1,024 characters
    Can you put the individual SUMIF's on each worksheet in the same place (say, cell B2), then on your summary page use this formula?

    =SUM(Sheet1heet19!B2)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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