Count values

[Roninn75]

hi
i have a sheet with 4 columns. the cells in those columns gets populate
with numbers. as well as a column for a persons name. on a differen
sheet i wish to calculate each column based on a specifi
criteria(person name).
so say my column names are A1 = apples, B1 = oranges, etc and i have
count of how many of each is sold by a specific salesman, when i selec
the salesman on a seperate sheet i wish to look through those 4 column
and match the salesman with the item then count how many of the item
was sold. hope that makes sense

 

[Don Guillett]

hi
i have a sheet with 4 columns. the cells in those columns gets populated
with numbers. as well as a column for a persons name. on a different
sheet i wish to calculate each column based on a specific
criteria(person name).
so say my column names are A1 = apples, B1 = oranges, etc and i have a
count of how many of each is sold by a specific salesman, when i select
the salesman on a seperate sheet i wish to look through those 4 columns
and match the salesman with the item then count how many of the items
was sold. hope that makes sense.

modify to suit.
=sumproduct((a1:a21="apples")*(b1:b21="name")*c1:c21)

 

[joeu2004]

i have a sheet with 4 columns. the cells in those
columns gets populated with numbers. as well as a
column for a persons name. on a different sheet i
wish to calculate each column based on a specific
criteria(person name).
so say my column names are A1 = apples, B1 = oranges,
etc and i have a count of how many of each is sold
by a specific salesman, when i select the salesman
on a seperate sheet i wish to look through those 4
columns and match the salesman with the item then
count how many of the items was sold.
hope that makes sense.

Not entirely. First, for some interpretations, it might help if you
mentioned the Excel version. Second, it would help if you gave us all the
sheet names and column references.

Third, it is unclear to me whether the data sheet (the first sheet you
mention) contains one row per sales person or one row per transaction, i.e.
each time a person sold one or more items.

It is also unclear to me whether you want to count each product (item) by
each sales person (i.e. 4 columns), or if you want to count all items sold
by each sales person (i.e. a single cell).

Suppose in Sheet1 (transactions), you have product names in A1 through D1
and row for each sale starting in row 2 with the sales person's name in
column E.

And suppose in Sheet2 (summary), you have each sales person's name in column
A starting in row 2 and the product names in B1 through E1.

Then put the following formula into B2 and copy down and across:

=SUMPRODUCT(--(Sheet1!$E$2:$E$1000=$A2),Sheet1!A$2:A$1000)

Be careful with the dollar signs ("$"): the use of absolute and mixed
references is chosen to make it easy for you to copy down and across.

Note that it is not necessary to compare with the product name since I
assume that the order of the product names in Sheet1!A is the same as in
Sheet2!B:E.

Caveat: In XL2007 and later, we can write Sheet1!$E:$E and Sheet1!A:A.
Avoid the temptation. That is extremely inefficient since it will construct
one array parameter of 1M elements (1M = 1,048,576), and SUMPRODUCT will
perform 1M compound operations.

If my interpretation of your situation is incorrect, I suggest that you
upload an example Excel file to a file-sharing website and post the URL (aka
link; http://...) of the shared uploaded file here. The following is a list
of some free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com