Sum salespeople by day in a full range

**Frenchii** · 04-05-2011, 01:01 PM

HI,
I am trying to create a sum based on how many customers a salesperson saw for a day.
I am working with a years worth of data, for each day I have salespeople visiting customers and
need to get a sum by day by salesperson.

I am usually able to create formulas basing my counts by concatenating the date/salesperson and and using the change in date/salesperson.

Since this is a years worth of data I dont want to create short ranges, is there a way to do this on a full set of data (1600 rows). Again, looking for sum by date for each salesperson when there are multiple salespeople in a day. In my attachment the column 'COUNT concat' is the result I would like to produce in some fashion.

Id even be happy trying to back out into it, creating a 'countif' statement
=IF(COUNTIF($V$2:V2,V2)=1,1,0)
where it recognizes the change '1' and can sum the range to the previous '1' count.

Thanks,
Frenchii

**arthurbr** · 04-05-2011, 01:09 PM

Hi and welcome to the board

Have you tried using a Pivot Table ?

**Marcol** · 04-05-2011, 01:22 PM

Maybe on these lines

Please Login or Register  to view this content.

Hope this helps

**Frenchii** · 04-05-2011, 02:00 PM

HI,
the count doesnt seem to sum correctly in the attachment and I am trying to avoid inputting a range, I would like the range to be my worksheet (C2:C1600), and do not want to put the condition for a salesperson, as that is too much work for 1600 rows of data.

I am trying to get the sum so I can pull it in a pivot table along with some shift information.

Thanks!

**Marcol** · 04-05-2011, 02:15 PM

My mistake with the formula, forgot to change the last parameter to match the data table

Sort the original Data on Date then by Sales
Then

Please Login or Register  to view this content.

1600 rows is not excessive for sumproduct, I accept it is calculating every cell x1600 each time there is a recalculation, but that is not too bad. (Others might disagree.)

**Frenchii** · 04-05-2011, 02:39 PM

I was able to get it by doing a lookup on the concatenated field.
=SUMIF(W:W,AB2,V:V)

See the attached, not sure why I couldnt get this to work earlier.

Thanks I'm smarter than I thought !!

**Marcol** · 04-05-2011, 03:06 PM

Okay, I usually use helper columns myself, others seem to want to do without them.

There are pros and cons to both methods, none of which are really noticable until the sheet gets really big.