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

 

[kweaver]

Because the formulas are looking in G and E, etc. -- I would move the RESULTS to the left (e.g., starting in the D column) such as:

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

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

 

[deletedalien]

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

<tbody>
[TD="align: center"]53[/TD]
[TD="align: right"]4250004[/TD]
[TD="align: right"] 115,464.78[/TD]

[TD="align: center"]54[/TD]
[TD="align: right"]4250017[/TD]
[TD="align: right"] 197,053.27[/TD]

</tbody>

SalesPeople​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]F53[/TH]
[TD="align: left"]=SUMIF($G$1:$G$1000,E53,$I$1:$I$1000)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

yup, you are absolutely correct,

except in order to do that...
i would need to bring in a vlookup to obtain the salesman # and then do a sumif....
and yes that would tottaly work...

Except since i can't keep a "database" of all salesmen in the same workbook (legal stuff) i would need to reference an external workbook and such...

its a great suggestion...
but it won't work for my particular case...

 

[kweaver]

If you don't want to type in the salesman numbers, you could go back to my previous example and use:

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

Cell Formulas
Range	Formula
E53	=SUMIF($G$1:$G$1000,D53,$I$1:$I$1000)
F53	=COUNTIF($G$1:$G$1000,D53)
D53	{=INDIRECT("G"&SMALL(IF(ISNUMBER(SEARCH("Salesman",$G$1:$G$1000)),ROW($G$1:$G$1000),""),(ROW()-52))+1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Logit]

.
You should follow kweaver 's example. It will require a change in your layout but some times that is required to obtain the desired results.

 

[deletedalien]

i want to....
its the easiest way....
but i cant...

 

[deletedalien]

i think what i might HAVE to do is.....

get unique values....


Array
{=IFERROR(INDEX($G$10:$G$47, MATCH(0,COUNTIF($F$40:F43, $G$10:$G$47), 0)),"")}

Then....
countif... the result of those unique values...

 

[deletedalien]

If you don't want to type in the salesman numbers, you could go back to my previous example and use:

Excel 2010

	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]53[/TD]
[TD="align: right"]4250004[/TD]
[TD="align: right"] 115,464.78 [/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]54[/TD]
[TD="align: right"]4250017[/TD]
[TD="align: right"] 197,053.27 [/TD]
[TD="align: right"]7[/TD]

</tbody>

SalesPeople​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E53[/TH]
[TD="align: left"]=SUMIF($G$1:$G$1000,D53,$I$1:$I$1000)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F53[/TH]
[TD="align: left"]=COUNTIF($G$1:$G$1000,D53)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D53[/TH]
[TD="align: left"]{=INDIRECT("G"&SMALL(IF(ISNUMBER(SEARCH("Salesman",$G$1:$G$1000)),ROW($G$1:$G$1000),""),(ROW()-52))+1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

i KNOW i have to be doing something wrong.....

your array formula gives me an #num !...... error...
(i did use ctrl+shift+enter)

 

[kweaver]

You'll have to clarify why you can't use the suggested approach. If you are showing the salesman numbers in the G column, why can't you have them inserted by formula or typing them in the results section?
It's not like you're hiding them in G ????

 

[kweaver]

You will get a #num error if you placed the results block w/this array formula in another section of the worksheet.

If the results section BEGINS in D53, it will work. If you've placed it starting in, say, D100, it will NOT work without changing the 52 to 99 in this case.

I said as much in an earlier post when I used this technique.

 

[deletedalien]

Because i can't use the salesman ID.

i would preffer to use the salesman name.

Salesman	Total $	Total sales
Person1	$115,464.78	5
Person2	$197,053.27	7

<tbody>
</tbody>