software

I have to calculate the commission split of a selling agent based on what he
has been closing during the last 365 days.

These are the columns that are involved in this calculation
H N
Total sale - closing date

When I enter the latest closing date (plus the corresponding total sale),
the system reviews this date and the previous ones (about 30 of 40 records)
and check which ones falls in the “365 days” condition, and then, gives as
result the sum of the total sales of those days that enter in that condition.

I used a IF formula but, only allowed me 7 conditions…

=(IF((N31-$N$2)lt;=365,SUM($H$2:H31),IF((N31-$N$3)lt;=365,SUM($H$3:H31),(IF((N31-$N$4)lt;=365,SUM($H$4:H31),(IF((N31-$N$5)lt;=365,SUM($H$5:H31),(IF((N31-$N$6)lt;=365,SUM($H$6:H31),(IF((N31-$N$7)lt;=365,SUM($H$7:H31),(IF((N31-$N$8)lt;=365,SUM($H$8:H31),0)))))))))))))

Could anyone help me with this?

ThanksIf I understand correctly, you're using the series of IFs to determine the
'cutoff' item and adding all the entries following. I think this can be
handled more easily and flexibly with a SUMIF function: =sumif(n$2:n31,quot;gt;=quot;
amp; (n31-365),h$2:h31).
This will check all entries in the range n2:n31 for dates in the range
[n31-365,n1] and add the corresponding sale amount in h2:h31 when the date
condition is satisfied.
Am I interpreting correctly?
--Bruce

quot;haidithquot; wrote:

gt; I have to calculate the commission split of a selling agent based on what he
gt; has been closing during the last 365 days.
gt;
gt; These are the columns that are involved in this calculation
gt; H N
gt; Total sale - closing date
gt;
gt; When I enter the latest closing date (plus the corresponding total sale),
gt; the system reviews this date and the previous ones (about 30 of 40 records)
gt; and check which ones falls in the “365 days” condition, and then, gives as
gt; result the sum of the total sales of those days that enter in that condition.
gt;
gt; I used a IF formula but, only allowed me 7 conditions…
gt;
gt; =(IF((N31-$N$2)lt;=365,SUM($H$2:H31),IF((N31-$N$3)lt;=365,SUM($H$3:H31),(IF((N31-$N$4)lt;=365,SUM($H$4:H31),(IF((N31-$N$5)lt;=365,SUM($H$5:H31),(IF((N31-$N$6)lt;=365,SUM($H$6:H31),(IF((N31-$N$7)lt;=365,SUM($H$7:H31),(IF((N31-$N$8)lt;=365,SUM($H$8:H31),0)))))))))))))
gt;
gt; Could anyone help me with this?
gt;
gt; Thanks
gt;

Hi Bruce
Thanks so much. This is exactly what i was needing.

Thanks again for your help.

quot;bpeltzerquot; wrote:

gt; If I understand correctly, you're using the series of IFs to determine the
gt; 'cutoff' item and adding all the entries following. I think this can be
gt; handled more easily and flexibly with a SUMIF function: =sumif(n$2:n31,quot;gt;=quot;
gt; amp; (n31-365),h$2:h31).
gt; This will check all entries in the range n2:n31 for dates in the range
gt; [n31-365,n1] and add the corresponding sale amount in h2:h31 when the date
gt; condition is satisfied.
gt; Am I interpreting correctly?
gt; --Bruce
gt;
gt; quot;haidithquot; wrote:
gt;
gt; gt; I have to calculate the commission split of a selling agent based on what he
gt; gt; has been closing during the last 365 days.
gt; gt;
gt; gt; These are the columns that are involved in this calculation
gt; gt; H N
gt; gt; Total sale - closing date
gt; gt;
gt; gt; When I enter the latest closing date (plus the corresponding total sale),
gt; gt; the system reviews this date and the previous ones (about 30 of 40 records)
gt; gt; and check which ones falls in the “365 days” condition, and then, gives as
gt; gt; result the sum of the total sales of those days that enter in that condition.
gt; gt;
gt; gt; I used a IF formula but, only allowed me 7 conditions…
gt; gt;
gt; gt; =(IF((N31-$N$2)lt;=365,SUM($H$2:H31),IF((N31-$N$3)lt;=365,SUM($H$3:H31),(IF((N31-$N$4)lt;=365,SUM($H$4:H31),(IF((N31-$N$5)lt;=365,SUM($H$5:H31),(IF((N31-$N$6)lt;=365,SUM($H$6:H31),(IF((N31-$N$7)lt;=365,SUM($H$7:H31),(IF((N31-$N$8)lt;=365,SUM($H$8:H31),0)))))))))))))
gt; gt;
gt; gt; Could anyone help me with this?
gt; gt;
gt; gt; Thanks
gt; gt;