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

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.

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

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


On Fri, 25 Aug 2006 07:25:02 -0700, Jakki
wrote:

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.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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

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)

"Jakki" wrote in message
...
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.

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

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.

"Richard Buttrey" wrote:

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


On Fri, 25 Aug 2006 07:25:02 -0700, Jakki
wrote:

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.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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

Subtract COUNT(I206:I5723,"NEU2")

--
HTH

Bob Phillips

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

"Jakki" wrote in message
...
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:I57
23="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.

"Richard Buttrey" wrote:

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


On Fri, 25 Aug 2006 07:25:02 -0700, Jakki
wrote:

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.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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

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.

"Bob Phillips" wrote:

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)

"Jakki" wrote in message
...
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.

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

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

Subtract COUNTIF(I206:I5723,"NEU2")

Hope this helps.

Pete

Jakki wrote:
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.

"Bob Phillips" wrote:

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)

"Jakki" wrote in message
...
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.

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

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.

"Pete_UK" wrote:

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

Subtract COUNTIF(I206:I5723,"NEU2")

Hope this helps.

Pete

Jakki wrote:
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.

"Bob Phillips" wrote:

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)

"Jakki" wrote in message
...
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.

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

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

Jakki wrote:
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.

"Pete_UK" wrote:

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

Subtract COUNTIF(I206:I5723,"NEU2")

Hope this helps.

Pete

Jakki wrote:
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.

"Bob Phillips" wrote:

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)

"Jakki" wrote in message
...
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.