You don't say how many worksheets/books you have to summarise.

If it's hundreds of them then automation is the only way to go, but if we
are talking a dozen or so, or anything around that, then personally i would
probably just dump them all into a single worksheet, one under the other and
then throw a Pivot table at it.

Now, if you want the total number of items, you will need to either:-

Add a total column to your data that sums the 3 items,

or (and my preference),

is duplicate your data 3 times over, one under the other (Only when you have
consolidated them all though - only need to do it once), and in a new column
titled Refreshment give each section of data it's own flag saying either
Lemonade/Coke/Pretzel, then delete two items from each section of data, (eg
from the Lemonade section delete the Coke and Pretzels data, from the Coke
section delete the Lemonade and Pretzels data) such that instead of this

SP      ID   Client   L   C   P  
John    1    Smith  2   3   1
John    1    Smith  3   1   2
John    1    Smith  4   2   2
John    1    Smith  3   3   3

you get this:-

SP      ID   Client  Amt  Ref  
John    1    Smith  2    Lem
John    1    Smith  3    Lem
John    1    Smith  4    Lem
John    1    Smith  3    Lem
John    1    Smith  3    Coke
John    1    Smith  1    Coke
John    1    Smith  2    Coke
John    1    Smith  3    Coke
John    1    Smith  1    Pretzel
John    1    Smith  2    Pretzel
John    1    Smith  2    Pretzel
John    1    Smith  3    Pretzel

A pivot table would then allow you to analyse that lot in literally no more
than a minute.

Select all data, do Data / Pivot table and chart report / Next / Next / Finish

Drag Salesman into ROW field, Drag Refreshment into COLUMN field, Drag Amt
into DATA field, and then drag Client to BETWEEN ROW and DATA field. Done.

Doing it this way also allows you to analyse the breakdown of how much of
each product was actually sold, and by who.