HELP! Simple (?) Data Counting

[patryan22]

I am having a LOT of trouble with what seems to me should be a fairly
simple function.

I am setting up a sort of database for our sales department. It
contains 16 criteria headings. At the end of each day, the manager will
go in and enter the information for each sale (1 sale will occupy one
row in the list). Most of the fields have pull-down menus for
consistency of information. I consider this the raw data.

Next, I have 7 worksheets, each dedicated to an individual salesman.
These sheets are identical in format. Each individual product is
listed down the left-hand side (there are 49 different products). The
months are listed across the top.

In the end, I want to be able to look at Salesman Steve's sheet and see
a simple count of how many units of Product A he sold in November, for
example. This seems like it should be simple. I want to see how many
rows in the database meet three specifications: Salesman Steve,
Product A, and November. (Salesman, Product, and Month ARE three of
the criteria headings on the data page.)

I would greatly appreciate any help anybody could give me. If you need
any more information/details I would be glad to provide it.

Pat

 

[Ken Wright]

The SUMPRODUCT function will do that, and so will a simple Pivot table.

SUMPRODUCT:-
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
=SUMPRODUCT(--(Sheet1!A1:A30="fred Bloggs"),--(Sheet1!B1:B30="Product
A"),--(Sheet1!C1:C30="November"))

Pivot Tables:-
http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm


You are likely to be adding new data regularly so should try and make your
ranges dynamic.
http://www.contextures.com/xlPivot01.html

Personally I'd look at Pivot tables, as they are well suited to that kind of
raw data.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------