Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count values
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count values
On Feb 20, 1:23*am, Roninn75 wrote:
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. -- Roninn75 modify to suit. =sumproduct((a1:a21="apples")*(b1:b21="name")*c1:c 21) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count values
"Roninn75" wrote:
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:D 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 MediaFi http://www.mediafire.com FileFactory: http://www.filefactory.com FileSavr: http://www.filesavr.com FileDropper: http://www.filedropper.com RapidSha http://www.rapidshare.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Using the COUNT funciton to count cells with values | Excel Programming |