PDA

View Full Version : Problem with a Formula



qazgosu
2012-05-07, 12:42 PM
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

CheshireCat
2012-05-07, 07:13 PM
Hi,

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



=C5/F5:F5


this is the reason you are getting the #DIV/0! error.

Try modifying it to:



=IF(F5=0,0,C5/F5)


Cheers,

qazgosu
2012-05-08, 07:17 AM
Just perfect !
Thx a lot !!! :D