'variable' sumif range? based on a given value...

[gsrmini]

Any suggestions on how I could incorporate a variable range for my sumif equation?

I have a large data chart which holds sales information.
starts with salesperson initial, project number, project name, revenue, expense etc.

My current sumif is adding values recorded in the chart, based on salesperson initial. This allows me to have a salesperson summary based on all the projects listed in the chart.

Currently this will give me every value or calculation that I need based on entire chart amounts.
I'd like to be able to somehow, based on a cell value, change the range of data to be added (etc.) based on salesperson initial.

Possibly using project number? The project number will progressively increase as the chart builds.

Anybody have any suggestions of what I should use to better calculate sum amounts, based on salesperson initial (on a variable range, perhaps if project number is greater than (specified))?

Thanks,

 

[T. Valko]

Any suggestions on how I could incorporate a variable range for my sumif equation?

I have a large data chart which holds sales information.
starts with salesperson initial, project number, project name, revenue, expense etc.

My current sumif is adding values recorded in the chart, based on salesperson initial. This allows me to have a salesperson summary based on all the projects listed in the chart.

Currently this will give me every value or calculation that I need based on entire chart amounts.
I'd like to be able to somehow, based on a cell value, change the range of data to be added (etc.) based on salesperson initial.

Possibly using project number? The project number will progressively increase as the chart builds.

Anybody have any suggestions of what I should use to better calculate sum amounts, based on salesperson initial (on a variable range, perhaps if project number is greater than (specified))?

Thanks,

It would help if you could post a small amount of sample data so we can see you layout.

 

[gsrmini]

My Data chart:
<TABLE style="WIDTH: 869pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1157><COLGROUP><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6838" width=187><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><COL style="WIDTH: 123pt; mso-width-source: userset; mso-width-alt: 5997" width=164><TBODY><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 45pt; HEIGHT: 30.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79 height=41 width=60>Salesperson
Initial

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 70pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=93>Job #</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 140pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=187>Name</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 89pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 width=119>Total Revenue</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 86pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 width=114>Total Expense</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 89pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78 width=119>Overhead Exp.</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 width=94>Overhead %</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=78>(C) commercial
(R) residential

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 97pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=129>Total
Approx. Income

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 123pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=164>Total Approx. Income
% of Total Revenue

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 height=20>AA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>1600</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>Project Name</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>$ I Enter this Value</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>$I Enter Value</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77>$Calculates based
on percentage -->


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74>based on
c or r%


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>I enter c or r</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: #c00000; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-background-source: auto; mso-pattern: auto; text-underline-style: none; text-line-through: none" class=xl76>-$revenue-(expense + overhead amnt)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: #c00000; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-background-source: auto; mso-pattern: auto; text-underline-style: none; text-line-through: none" class=xl75>% between approx income and revenue.
(income %)


</TD></TR></TBODY></TABLE>
(Currently used rows 6-205)

My salesperson summary currently uses sumif based on salesperson initial.
I use it to summarize total approx. income on projects.
I have another calculation which will then give me an average percent income on revenue, per salesperson.

Need to find a way to have a salesperson summary, which i can modify the range, as I may not always want values based on entire chart amounts.

Any more clear?