I need to caluclate a formula based on the results of multiple other cells.
At present I use the following formula which calculates firstly if the "sale" is within a certain date range, secondly if the area of sale is within an area, thirdly if it matches a group of sales area, fourth if it is not another type of sale and the fifth relates to the date range as well.
Question - this forumla works well and counts the number of sales. However how do I make it now calculate a formula based on this sale information. Formula 2 represents the formula I want to calculate, but at present I have a seperate page that looks at 5000 lines of data and does 5000 calculations. I can't seem to get the 2 formulas into 1. Is anyone of any help (I'm ossyr if this is bloody confusing
Formula 1.
{=SUM(IF(Data!$T$2:$T$5000>Main!$B$2-8,IF(Data!$B$2:$B$5000=$A3,IF(Data!$D$2:$D$5000="Payment Services Officer",IF(Data!$U$2:$U$5000="",IF(Data!$T$2:$T$5000<Main!$B$2,1,0),0),0),0),0),0)}
Formula 2.
=IF(AND(Data!D2="Payment Services Officer",Data!Q2>Main!$B$2-8,Data!V2="Won"),SUM(Data!H2*Data!O2*12/100),0)
At present I use the following formula which calculates firstly if the "sale" is within a certain date range, secondly if the area of sale is within an area, thirdly if it matches a group of sales area, fourth if it is not another type of sale and the fifth relates to the date range as well.
Question - this forumla works well and counts the number of sales. However how do I make it now calculate a formula based on this sale information. Formula 2 represents the formula I want to calculate, but at present I have a seperate page that looks at 5000 lines of data and does 5000 calculations. I can't seem to get the 2 formulas into 1. Is anyone of any help (I'm ossyr if this is bloody confusing
Formula 1.
{=SUM(IF(Data!$T$2:$T$5000>Main!$B$2-8,IF(Data!$B$2:$B$5000=$A3,IF(Data!$D$2:$D$5000="Payment Services Officer",IF(Data!$U$2:$U$5000="",IF(Data!$T$2:$T$5000<Main!$B$2,1,0),0),0),0),0),0)}
Formula 2.
=IF(AND(Data!D2="Payment Services Officer",Data!Q2>Main!$B$2-8,Data!V2="Won"),SUM(Data!H2*Data!O2*12/100),0)