Results 1 to 3 of 3

Thread: Problem with a Formula

  1. #1

    Problem with a Formula



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

    Hello , i m sending you a test XLS file.
    there are 2 Sheets , all sales and monthly summary

    In the monthly summary i want to make a sum of all Incomes / Outcome from ALL SALES for every month BUT
    i want to put a big range of CELLS cause the XLS file isnt yet filled for all the months of the year

    I tried this formula
    =SUMPRODUCT((MONTH('ALL SALES'!B4:B2000)=1)*('ALL SALES'!J4:J2000))
    but i get a DIV/0

    IF i do it like this
    =SUMPRODUCT((MONTH('ALL SALES'!B4:B124)=1)*('ALL SALES'!J4:J124))
    where 124 are the cells which are filled it works perfectly but i need a big range of cells so i can fill data and the sum is being automatic to sheet 2 ( MONTHLY SUMMARY)

    THX a lot !
    PS) ATTACH OF FILE on
    Attached Files Attached Files

  2. #2
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    121
    Articles
    0
    Excel Version
    Microsoft Excel 2013
    Hi,

    The formula you are using in the "TOTAL" column of the "ALL SALES" worksheet is:

    Code:
    =C5/F5:F5
    this is the reason you are getting the #DIV/0! error.

    Try modifying it to:

    Code:
    =IF(F5=0,0,C5/F5)
    Cheers,
    Last edited by CheshireCat; 2012-05-07 at 07:16 PM.

  3. #3
    Just perfect !
    Thx a lot !!!

Posting Permissions

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