=SUMPRODUCT(--(d18:d117<>""),--(Or(G18:G117="C",G18:G117="D",G18:G117="N",G18:G117="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 are: C, M, & S.
>
> The categories are: 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,
>
|
Similar Threads
