count sales per Salesman

[deletedalien]

so.....

this seems to be pretty straightforward but for the life of me i can't seem to think of a formula that could work with this....


i have a list of salesmen who well.... made sales...

and i need to count the amount of sales per salesman, however, the salesman nema only shows up once per sales report...

See file here:
https://1drv.ms/x/s!Agyr_mEIeS75gaZsHzHC_kd_G-5sGg?e=dEze1a

how can i count the total lines per salesman,

say

salesman 12355, who sold 115,464.78, he has a total of 5 sales (5 lines under him...)

Salesman 12587 who sold 197,053.27 He has a total of 7 sales (has 7 lines under him...)


this is a short report but some of these include up to 30 salesmen.

is this even possible?

Thanx in advance

 

[Logit]

Why do the salesmen ID number change from Col A to Col G ?

Can they not be the same ?

 

[kweaver]

I came up with a formula that needs two helper cells if you can live with that.

I happened to use K36 and L36 (and filled them down for however many sales people you have in F36, etc.)

K36 is:

=SMALL(IF(ISNUMBER(SEARCH("Amount",$I$1:$I$1000)),ROW($I$1:$I$1000),""),(ROW()-35))

and is an array formula resolved with CTRL+Shift+Enter

L36 is:

=SMALL(IF(ISNUMBER(SEARCH("total",$H$1:$H$1000)),ROW($H$1:$H$1000),""),(ROW()-35))-1

and is also an array formula.

Then, fill down this formula in F36 (shifted your totals and count over one column to the left):

=SUM(INDIRECT("I"&K36):INDIRECT("I"&L36))

(NOT an array formula)

and in G36 filled down:

=L36-K36

(NOT an array formula)

Excel 2010
	E	F	G	H	I	J	K	L
35	Salesman
36	12355	115464.78	5				9	14
37	12587	197053.27	7				24	31
Sheet2

Cell Formulas
Range	Formula
F36	=SUM(INDIRECT("I"&K36):INDIRECT("I"&L36))
G36	=L36-K36
K36	{=SMALL(IF(ISNUMBER(SEARCH("Amount",$I$1:$I$1000)),ROW($I$1:$I$1000),""),(ROW()-35))}
L36	{=SMALL(IF(ISNUMBER(SEARCH("total",$H$1:$H$1000)),ROW($H$1:$H$1000),""),(ROW()-35))-1}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kweaver]

Mine is untested for additional groups of sales people.

If you move the totals section at the bottom down some N number of rows, you'll need to change my formulas accordingly including the (ROW()-35) parts to be (ROW()-??) where ?? will be whatever number subtracted from the row you're in for the 1st salesman that will yield a 1 as the result. So, if the K36 formula ends up being in K100, you'll need (ROW()-99). Clear?

Oh, I also corrected your spelling of "Amount" from "Ammount" to "Amount". If you want it to be with two Ms, change my formulas accordingly.

 

[Logit]

.
Using just what you have presented in your existing workbook ... in H36, paste this formula :

=COUNTIF(G1:G1000,4250004)

In H37 use this formula:

=COUNTIF(G1:G1000,4250017)

You can edit the range for Col G as required, as well as edit the Salesman ID number, in the formula.

 

[kweaver]

Logit is 100% correct and simple as long as there's a unique correlation between 12355 sales person and 4250004 sales person, etc.

 

[deletedalien]

Why do the salesmen ID number change from Col A to Col G ?

Can they not be the same ?

sorry bout that...

that was a typo on my end...

the ones in vol A should be names....
the ones in vol G are employee (or salesman) ID

 

[Logit]

.
My Post #5 formulas should do the job for you.

 

[deletedalien]

.
Using just what you have presented in your existing workbook ... in H36, paste this formula :

=COUNTIF(G1:G1000,4250004)

In H37 use this formula:

=COUNTIF(G1:G1000,4250017)

You can edit the range for Col G as required, as well as edit the Salesman ID number, in the formula.

true.....
except the salesman shown in this report arent always the same

 

[kweaver]

If you have the sales people listed in, say, E53 and E54, etc., etc., then the total amount for them would be using Logit's formula:

=SUMIF($G$1:$G$1000,E53,$I$1:$I$1000)

Excel 2010
	E	F
53	4250004	115,464.78
54	4250017	197,053.27
SalesPeople

Cell Formulas
Range	Formula
F53	=SUMIF($G$1:$G$1000,E53,$I$1:$I$1000)