Help required in sumif function

[universeamol]

Hi,

I have some data of life insurance policies of my clients. I have appointed some agents for sales and i share my commission with them. I have got data with policy number, client name, agent's name, commission etc.

I have to derive a commission for a specific period for an an agent how the formula should be written? with sumif function i could derive the commission for an agent for the entire period. however how to get a specific period commission without applying data filter?

Please help me on this. Thank you.

 

[Akashwani]

Hi,

I assume you have a column with the Date, if you do then you may be able to use one of the following methods to Sum for a particular period given the start and end date.

This is sample data and example results, you will probably have to alter the formulas provided to suit your layout and requirements...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Policy #	Client Name	Agent's Name	Commission	Date	*	Start Date	End Date	*	Agent	Total1	Total2	*
2	1234	Mr Jones	Ben	100	01/03/2013	*	10/02/2013	10/03/2013	*	Ben	400	400	*
3	1235	Mrs Jones	Bob	100	01/03/2013	*	*	*	*	Bob	400	400	*
4	1236	Mr Smith	Bill	100	03/03/2013	*	*	*	*	Bill	200	200	*
5	1237	Mrs Smith	Ben	100	04/03/2013	*	*	*	*	*	*	*	*
6	1238	Mr Green	Bob	100	05/03/2013	*	*	*	*	*	*	*	*
7	1239	Mrs Green	Bill	100	12/03/2013	*	*	*	*	*	*	*	*
8	1240	Mr Brown	Ben	100	13/03/2013	*	*	*	*	*	*	*	*
9	1241	Mrs Brown	Ben	100	14/03/2013	*	*	*	*	*	*	*	*
10	1242	Mr Black	Bill	100	15/03/2013	*	*	*	*	*	*	*	*
11	1243	Mrs Black	Bob	100	01/03/2013	*	*	*	*	*	*	*	*
12	1244	Mr White	Ben	100	01/03/2013	*	*	*	*	*	*	*	*
13	1245	Mrs White	Ben	100	03/03/2013	*	*	*	*	*	*	*	*
14	1246	Mr Grey	Bill	100	04/03/2013	*	*	*	*	*	*	*	*
15	1247	Mrs Grey	Bob	100	05/03/2013	*	*	*	*	*	*	*	*
16	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet5

The formulas used need to be copied down.

I hope this helps.

Good luck.

Ak

 

[rajeshksreedharan]

use subtotal function instead of sumif. subtotal will change accordingly as per your filter.

 

[Firefly2012]

Hi

Can you mock up some data for us and post it into a reply? If you use Internet Explorer the easiest way is to surround all your cells with a visible black border and then copy and paste your range into your reply in the browser.

You will need to have dates in your data to specify a range. Please also make sure you give your desired results, so we can ensure we are calculating the right thing.

 

[universeamol]

Thanks for your help.

However, I have dates in my data but only in one column. The date on which the premium was paid.

Here, I will enter the dates manually and will select the agent's name from drop down. Will the above formula will work this style?

PRIMARY COMMISSION

MUKUND V MORE

Insurance Professionals

Off No. 6, Heera Sadan, 508 B Narayan Peth, Pune 30. Contact : 94223 24111 / 2 443 0530

Agent Wise Commission

Policy Type Wise Commission

Client Wise Commission

From Date

To Date

From Date

To Date

From Date

To Date

Agent Name

Policy Name

Client Name

Commission

Commission

Commission

Total commissions till date

Total commissions till date

Total commissions till date

<COLGROUP><COL style="WIDTH: 48pt" span=13 width=64><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

 

[Akashwani]

Hi,

The above formula will not work with the layout that you have posted, sorry.

I may have misunderstood your layout, but this looks like a data entry sheet to me, once you have entered the information onto this sheet, do you store/copy it to another sheet?

The layout you have provided doesn't give any cell references, is this what you have?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	PRIMARY COMMISSION	*	*	*	*	*	*	*	*	MUKUND V MORE	*	*	*	*	*
2	*	*	*	Insurance Professionals	*	*	*	*	*	*	*	*	*	*	*
3	*	*	*	Off No. 6, Heera Sadan, 508 B Narayan Peth, Pune 30. Contact : 94223 24111 / 2 443 0530	*	*	*	*	*	*	*	*	*	*	*
4	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
5	Agent Wise Commission	*	*	*	*	Policy Type Wise Commission	*	*	*	*	Client Wise Commission	*	*	*	*
6	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
7	From Date	*	To Date	*	*	From Date	*	To Date	*	*	From Date	*	To Date	*	*
8	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
9	Agent Name	*	*	*	*	Policy Name	*	*	*	*	Client Name	*	*	*	*
10	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
11	Commission	*	*	*	*	Commission	*	*	*	*	Commission	*	*	*	*
12	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
13	Total commissions till date	*	*	*	*	Total commissions till date	*	*	*	*	Total commissions till date	*	*	*	*
14	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
15	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
16	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet7

The yellow cells represent the cells you would use to enter your data.

If you can provide an example of your data and the expected results, then I (we) may be able to better understand your layout and how to go about creating a solution for you.

Ak

 

[universeamol]

HI,

I am trying to upload the image of the sheet. However. I cannot insert the image from my computer. Do i have to activate something for this facility?

Or I can mail you the image so that you can view it quickly. Could you please post your mail ID.

Many thanks.

Amol.

 

[Akashwani]

Hi,

To post sample data, take a look here.....

http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

Or use this.....

Excel Jeanie Html

You could upload your file to somewhere like these....

Free File Hosting - Online Storage; Upload Mp3, Videos, Music. Backup Files
Free File Hosting - Online File Storage for Mp3, Videos, Music. Best File Host

Ak

 

[universeamol]

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	COMMISSION								MUKUND MORE
2									INAURANCE PROFESSIONALS
3								Office No. 6, Heera Sadan, 508 B Shaniwar Peth, Opp Fairdeal Paper Co. Pune 30. Contact : 020 24430530 / 94223 24111.
4
5	Agent Wise Commission					Policy Wise Commission					Client Wise Commission
6
7	From Date		To Date			From		To Date			From Date		To Date
8
9	Name	Amol				Name					Name
10
11	Commission					Commission					Commission
12
13	Total Commission earned till date			7,500.00		Total Commission earned till date					Total Commission earned till date			-
14
15
16	Date	Client Name			Policy No		Agent	DOC	Policy Type		S. A.	Term	Premium	Prem Type	Commission
17	24/03/2013	Amol Mali			123456789		Mukund	24/03/2013	Whole Life		1,000,000.00	25	100,000.00	Primary	5,000.00
18	24/03/2013	Mukund More			987654321		Amol	24/03/2013	Money Back (20)		1,000,000.00	20	50,000.00	Primary	7,500.00
19
20

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D13 =SUMIF(G17:G1048576,B9,O17:O1048576) N13 =SUMIF(B17:D1048576,L9,O17:O1048576) O17 =IF(P17="","",P17/2) O18 =IF(P18="","",P18/2) O19 =IF(P19="","",P19/2) O20 =IF(P20="","",P20/2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hi,

In the cell highlighted above in front of commission, I would like to derive values for the commission for a specific period for an agent.

Data will be entered below from A17 onwards and I will select the dates and agent name.

Hope this is crystal. Thank you for your help.

 

[Akashwani]

Hi,

This formula is based on the following assumptions...

The From Date is in A8
The To Date is in C8
The Agent name is in B9 and is entered as the full name and NOT just the first name.

=SUMPRODUCT(--(A17:A1048576>=A8)*(A17:A1048576<=C8)*(B17:B1048576=B9),(O17:O1048576))

I hope I have understood you correctly and this solves your problem.

Good luck.

Ak