Help required in sumif function

universeamol

New Member
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?

Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
ABCDEFGHIJKLM
1Policy #Client NameAgent's NameCommissionDate*Start DateEnd Date*AgentTotal1Total2*
21234Mr JonesBen10001/03/2013*10/02/201310/03/2013*Ben400400*
31235Mrs JonesBob10001/03/2013****Bob400400*
41236Mr SmithBill10003/03/2013****Bill200200*
51237Mrs SmithBen10004/03/2013********
61238Mr GreenBob10005/03/2013********
71239Mrs GreenBill10012/03/2013********
81240Mr BrownBen10013/03/2013********
91241Mrs BrownBen10014/03/2013********
101242Mr BlackBill10015/03/2013********
111243Mrs BlackBob10001/03/2013********
121244Mr WhiteBen10001/03/2013********
131245Mrs WhiteBen10003/03/2013********
141246Mr GreyBill10004/03/2013********
151247Mrs GreyBob10005/03/2013********
16*************
Sheet5

The formulas used need to be copied down.

I hope this helps.

Good luck.

Ak

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

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.

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>

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
ABCDEFGHIJKLMNO
1PRIMARY 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***************
5Agent Wise Commission****Policy Type Wise Commission****Client Wise Commission****
6***************
7From Date*To Date**From Date*To Date**From Date*To Date**
8***************
9Agent Name****Policy Name****Client Name****
10***************
11Commission****Commission****Commission****
12***************
13Total 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

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.

Excel 2007
ABCDEFGHIJKLMNO
1COMMISSIONMUKUND MORE
2INAURANCE PROFESSIONALS
3Office No. 6, Heera Sadan, 508 B Shaniwar Peth, Opp Fairdeal Paper Co. Pune 30. Contact : 020 24430530 / 94223 24111.
4
5Agent Wise CommissionPolicy Wise CommissionClient Wise Commission
6
7From DateTo DateFromTo DateFrom DateTo Date
8
9NameAmolNameName
10
11CommissionCommissionCommission
12
13Total Commission earned till date 7,500.00 Total Commission earned till dateTotal Commission earned till date -
14
15
16DateClient NamePolicy NoAgentDOCPolicy TypeS. A.Term Premium Prem Type Commission
1724/03/2013Amol Mali123456789Mukund24/03/2013Whole Life 1,000,000.00 25 100,000.00 Primary 5,000.00
1824/03/2013Mukund More987654321Amol24/03/2013Money Back (20) 1,000,000.00 20 50,000.00 Primary 7,500.00
19
20

</tbody>
Sheet1

Worksheet Formulas
CellFormula
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)

</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.

Last edited:
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

Replies
1
Views
384
Replies
1
Views
280
Replies
0
Views
226
Replies
10
Views
464
Replies
0
Views
194

1,196,308
Messages
6,014,573
Members
441,828
Latest member
cofracr

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.

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

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