Sum if with multiple options

[Shonky]

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
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)

[Aladin Akyurek]

On 2002-02-20 00:50, Shonky wrote:
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)

I'd like to have some clarification if possible.

1a) Activate an empty cell in Data, type =, select 10 rows of the data in T and U (including labels that you might have), hit F9, copy what you see after =-sign, and post it in the follow up.

1b) Do the same for the data in B and D.

2) Post what you have in Main!$B$2 along with what the constant 8 means.

3) Post what you have in Data!D2, Data!H2, and Data!V2 and what they mean.

As I understand it, you want to sum sales values that meet certain conditions.

Aladin

[Shonky]

1a. {"...Date Referral received","..Type of Amendment required";37180,"Key Entry";37180,"MOTO";37246,"MOTO";37176,0;37180,"MOTO";37281,"Key Entry";37298,0;37186,"MOTO";37196,0;37258,"Key Entry";37190,0}

1.b {" BUID NUMBER (XXXX-XXXXX)";"8173-47831";"7706-13716";"7706-40562";"8092-88659";"7709-57420";"8092-88659";"8090-80743";"7990-35673";"8090-81383";"8153-93738"}
{" Consultants Title";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer";"Payment Services Officer"}

2. Main!$b$2 is a date, being todays date.
-8 means please go back a week + 1 day.

D2 = Payment Services Officer (Job Title)
H2 = 5000 (Volume of dollars for sale)
V2 = Won (If it is a sale, ie a job can be another status, repair etc)

"As I understand it, you want to sum sales values that meet certain conditions. "

Yes, that is correct. Basically I have a database of data that I am doing reporting from, I dump it into excel. The reporting can be broked down by region (BUID Number), and I can count how many sales come from each region. I can't calculate what value collectively these sales contribue for each region. The sales have to be in a give time period (the last week), from a particular type of sales person (Payment Services Officer), if it meets that criteria I need to some the volume of sales projected for that sale (h2) times a particular discount rate (o2). I can calculate this no worries, but I can't get it to allocate these sales in to the region.

Thanks!

[Aladin Akyurek]

Looking closely at your 2nd formula, I realize that you must have data also in columns O, Q, and V in the Data worksheet.

Would you either post, following the procedure mentioned in our previous exchange, data from the columns I've missed in my previous request or send me a copy of your workbook?

Regards,

Aladin

akyurek@xs4all.nl