Can SUMPRODUCT be used to extract varying data in a column?

J

Jakki

I am working with counting student data based on term status, sex, college,
degree, campus and major. I have 9 columns of data (5519 records). I have
been using SUMPRODUCT to get a count of data in each column based on the
criteria I specify. Basically all columns have the same general data as noted
below:

TmSt Sx Col Lv Deg Cls Cm Prg Maj
FT M GN GN PHD G2 N BEN2_PHD BEN2
FT S GN GN PHD G2 N NEU2_PHD NEU2

My problem arises because the column labeled "Maj" has differing data based
on the program a student is pursuing. Is there a way this can be accomplished?

Thanking you in advance.
 
R

Richard Buttrey

It's not quite clear what it is you're trying to count in the Maj
column.

Your example below suggests that it's essentially the same as the Prg
column but with just the first 4 characters.

What do you mean by differing data, and could you give an example of
the formula and the result you expect?

Rgds


I am working with counting student data based on term status, sex, college,
degree, campus and major. I have 9 columns of data (5519 records). I have
been using SUMPRODUCT to get a count of data in each column based on the
criteria I specify. Basically all columns have the same general data as noted
below:

TmSt Sx Col Lv Deg Cls Cm Prg Maj
FT M GN GN PHD G2 N BEN2_PHD BEN2
FT S GN GN PHD G2 N NEU2_PHD NEU2

My problem arises because the column labeled "Maj" has differing data based
on the program a student is pursuing. Is there a way this can be accomplished?

Thanking you in advance.
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
B

Bob Phillips

Can't you just do the count based upon the columns other than Maj?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
J

Jakki

This is the formula I have used thusfar:

=SUMPRODUCT(($A$206:$A$5723="FT")*($B$206:$B$5723="M")*($C$206:$C$5723="GN")*($D$206:$D$5723="GN")*($E$206:$E$5723="PHD")*($G$206:$G$5723="N")-(I206:I5723="BEN2"))

Column I must also subtract "NEU2" to arrive a final total of 59. If I
subtract I206:I5723="NEU2" I receive an error message (#VALUE).

I appreciate your help.
 
B

Bob Phillips

Subtract COUNT(I206:I5723,"NEU2")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
J

Jakki

I wish it was that simple. There will be other rows of information that will
use the same formula(s) but the major will change. I tried your suggestion of
subtracting the other data element but it still will not give me the total of
59. The formula has not subtracted the 2 "NEU2" records.

Thanks for all your help.
 
P

Pete_UK

I think Bob meant to say (in posting no 4):

Subtract COUNTIF(I206:I5723,"NEU2")

Hope this helps.

Pete
 
J

Jakki

Thanks Pete_UK; tried your suggestion and I received a negative answer
(-220659). I have tried COUNT, COUNTIF, and SUMPRODUCT to no avail. Perhaps I
should be using a different function or formula? The one column of
information is really causing the problem.
 
P

Pete_UK

Jakki,

try putting the formula:

=COUNTIF(i206:i5723,"NEU2")

in a cell somewhere - it should return 2 if you only have 2 records, so
if you subtract this from your earlier SP formula I don't see how you
can get -220659, unless the SP formula itself is returning -220657

Pete
 
Top