formula to add sales

[leott]

I have a table rows: salesman sales date (mm/dd/yyyy) for many years of sales

I am trying to isolate total number of sales of each salesman by month and day (excluding year)

excel dates are giving me fits

example:
salesman sales date
man1 2 5/1/2018
man 2 3 5/2/2018
man 1 4 4/1/2011
man 3 2 3/1/2017
man 1 4 5/2/2009
man 1 4 5/1/2017

results:
man 3 2 3/1
man 1 4 4/1
man 1 6 5/1
man 1 7 5/2
man 2 3 5/2

Thanks for any suggestions

 

[alansidman]

Use a Pivot table to get the desired results. Dates in rows, Salesman in rows, Sales in Values. Ungroup the dates to show Months and years.

 

[kvsrinivasamurthy]

DATA
ROW B C date E
salesman sales date HELPER
3 man1 2 01-05-2018 1
4 man2 3 02-05-2018 1
5 man1 4 01-04-2011 1
6 man3 2 01-03-2017 1
7 man1 4 02-05-2009 1
8 man1 4 01-05-2017 0

Result table

ROW H I J
3 man3 2 3/1
4 man1 4 4/1
5 man1 6 5/1
6 man2 3 5/2
7 man1 4 5/2
8
9

Formulas
Helper Column E. In E3
=1*(SUMPRODUCT(($B$3:$B3=B3)*(MONTH($D$3:$D3)=MONTH(D3))*(DAY($D$3:$D3)=DAY(D3)))=1)
ARRAY formulas below
In H3

=IFERROR(INDEX($B$3:$B$8,SMALL(IF(SMALL(IF($E$3:$E$8=1,100*MONTH($D$3:$D$8)+DAY($D$3:$D$8),""),ROWS($J$3:$J3))=100*MONTH($D$3:$D$8)+DAY($D$3:$D$8),ROW($D$3:$D$8),""),COUNTIF($J$3:J3,J3))-ROW($B$3)+1),"")

In I3

=IFERROR(SUMPRODUCT(($C$3:$C$8)*($B$3:$B$8=H3)*(MONTH($D$3:$D$8)=--(LEFT(J3,FIND("/",J3)-1)))*(DAY($D$3:$D$8)=--MID(J3,FIND("/",J3)+1,2))),"")

In J3

=IFERROR(INDEX(MONTH($D$3:$D$8)&"/"&DAY($D$3:$D$8),MATCH(SMALL(IF($E$3:$E$8=1,100*MONTH($D$3:$D$8)+DAY($D$3:$D$8),""),ROWS($J$3:$J3)),100*MONTH($D$3:$D$8)+DAY($D$3:$D$8),0)),"")

How ARRAY formula is entered


Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.