how can i count multiple variables in multiple columns?
 

I am using Excel 2003.
I have a list of three people entering order data. These data are each
identified with a unique Order Serial Number.
These Serial Numbered Orders have 4 Category Classifications.
The data entries are listed in three separate columns. (equals 1 column
group).
These 3 column groups repeat 5 times in the spreadsheet.
I want to count the number of serial numbers entered by each person and by
each category, across the entire 5 column groups.

The people a C, M, & S.

The categories a C, D, N, U.

The order of entry is: Serial Number, Category, Person.

The Summary, by person, shows the total count of each Serial Number entered,
and total the count of each Swerial Number Category.

Summary example:

Person category Qty formula

S C 5 1a
S D 78 1b
S N 114 1c
S U 88 1d

C C 91 2a
C D 44 2b
C N 2 2c
C U 66 2d

M C 288 3a
M D 55 3b
M N 12 3c
M U 106 2d
etc.

sample array:


D G H

18 308601 U S
19 308602 N C
20 308603 C M
21 308604 D S

for person "S" the following formulas have been successful:
1a:
SUMPRODUCT(d18:d117<""),--(G18:G117="C"),--(H18:H117="S")
1b.
SUMPRODUCT(d18:d117<""),--(G18:G117="D"),--(H18:H117="S")
1c.
SUMPRODUCT(d18:d117<""),--(G18:G117="N"),--(H18:H117="S")
1d:
SUMPRODUCT(d18:d117<""),--(G18:G117="U"),--(H18:H117="S")

A repeat of the same, for persons "C" & "M" gets me the results I require
for 2a - 2d & 3a - 3d.

The problem is, I have to repeat the same formulas 4 more times to cover the
remaining 4 column groups. I must also add summary entries to each column
group.

Is there some way that i can combine terms to eliminate this extra work?
Thanks,

how can i count multiple variables in multiple columns?
 

=SUMPRODUCT(--(d18:d117<""),--(Or(G18:G117="C",G18:G117="D",G18:G117="N",G18:G11 7="U")),--(H18:H117="S"))

"GVPro" wrote:

I am using Excel 2003.
I have a list of three people entering order data. These data are each
identified with a unique Order Serial Number.
These Serial Numbered Orders have 4 Category Classifications.
The data entries are listed in three separate columns. (equals 1 column
group).
These 3 column groups repeat 5 times in the spreadsheet.
I want to count the number of serial numbers entered by each person and by
each category, across the entire 5 column groups.

The people a C, M, & S.

The categories a C, D, N, U.

The order of entry is: Serial Number, Category, Person.

The Summary, by person, shows the total count of each Serial Number entered,
and total the count of each Swerial Number Category.

Summary example:

Person category Qty formula

S C 5 1a
S D 78 1b
S N 114 1c
S U 88 1d

C C 91 2a
C D 44 2b
C N 2 2c
C U 66 2d

M C 288 3a
M D 55 3b
M N 12 3c
M U 106 2d
etc.

sample array:


D G H

18 308601 U S
19 308602 N C
20 308603 C M
21 308604 D S

for person "S" the following formulas have been successful:
1a:
SUMPRODUCT(d18:d117<""),--(G18:G117="C"),--(H18:H117="S")
1b.
SUMPRODUCT(d18:d117<""),--(G18:G117="D"),--(H18:H117="S")
1c.
SUMPRODUCT(d18:d117<""),--(G18:G117="N"),--(H18:H117="S")
1d:
SUMPRODUCT(d18:d117<""),--(G18:G117="U"),--(H18:H117="S")

A repeat of the same, for persons "C" & "M" gets me the results I require
for 2a - 2d & 3a - 3d.

The problem is, I have to repeat the same formulas 4 more times to cover the
remaining 4 column groups. I must also add summary entries to each column
group.

Is there some way that i can combine terms to eliminate this extra work?
Thanks,