Sum if with multiple options

Shonky

New Member
Joined
Feb 19, 2002
Messages
41
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)
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top