Problem with a Formula

qazgosu

New member
Joined
May 7, 2012
Messages
2
Reaction score
0
Points
0
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
 

Attachments

  • test.xlsx
    41.9 KB · Views: 15

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
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:
Top