1. Registered User
Join Date
Jan 2004
Location
Posts
151

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. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
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.

3. Registered User
Join Date
Jan 2004
Location
Posts
151
sorry iam not to good with VB how would i do that

4. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Wait... you're only supplying two arguments to SUMIF() and it wants three... How does that work?

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

6. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
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.

7. Registered User
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)

#### Posting Permissions

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