I have an EEO spreadsheet that I need to put an array formula for Sex, Race,
& EEO category. I have Sex in the "C" column, Race in the "D" column, and
EEO in the "G" column, and "H7" for the Male gender match, "I7" for "01" Race
code match, and EEO category 01 is in "J7". I can get my array to work if I
delete the last EEO segment in the formula out, but I cannot get it to work
with it. Do I have too many segments for it to work? Below is my formula:
Please help. Thank you.

=SUM(IF($C$4:$C$443=$H$7,IF($D$4:$D$443=$I$7,IF($G$4:$G$443=$J$7,1,0),0)))

Unless you have Excel 2007 with SUMIFS you need to use SUMPRODUCT

=SUMPRODUCT(--($C$4:$C$443=$H$7),--($D$4:$D$443=$I$7),--($G$4:$G$443=$J$7))

Will tell you how may records have C-cell = H7 and D-cell =I7 and G-cell =
J7
For more details see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Shazam" <(E-Mail Removed)> wrote in message
news:A7006BD1-C907-43AE-9AED-(E-Mail Removed)...
>I have an EEO spreadsheet that I need to put an array formula for Sex,
>Race,
> & EEO category. I have Sex in the "C" column, Race in the "D" column, and
> EEO in the "G" column, and "H7" for the Male gender match, "I7" for "01"
> Race
> code match, and EEO category 01 is in "J7". I can get my array to work if
> I
> delete the last EEO segment in the formula out, but I cannot get it to
> work
> with it. Do I have too many segments for it to work? Below is my
> formula:
> Please help. Thank you.
>
> =SUM(IF($C$4:$C$443=$H$7,IF($D$4:$D$443=$I$7,IF($G$4:$G$443=$J$7,1,0),0)))
>

I would think it very likely that you'd do better with a Pivot Table:

http://peltiertech.com/Excel/Pivots/pivotstart.htm


In article <A7006BD1-C907-43AE-9AED-(E-Mail Removed)>,
Shazam <(E-Mail Removed)> wrote:

> I have an EEO spreadsheet that I need to put an array formula for Sex, Race,
> & EEO category. I have Sex in the "C" column, Race in the "D" column, and
> EEO in the "G" column, and "H7" for the Male gender match, "I7" for "01" Race
> code match, and EEO category 01 is in "J7". I can get my array to work if I
> delete the last EEO segment in the formula out, but I cannot get it to work
> with it. Do I have too many segments for it to work? Below is my formula:
> Please help. Thank you.
>
> =SUM(IF($C$4:$C$443=$H$7,IF($D$4:$D$443=$I$7,IF($G$4:$G$443=$J$7,1,0),0)))

try:

=SUMPRODUCT(--($C$4:$C$443=$H$7),--($D$4:$D$443=$I$7),--($G$4:$G$443=$J$7))


"Shazam" wrote:

> I have an EEO spreadsheet that I need to put an array formula for Sex, Race,
> & EEO category. I have Sex in the "C" column, Race in the "D" column, and
> EEO in the "G" column, and "H7" for the Male gender match, "I7" for "01" Race
> code match, and EEO category 01 is in "J7". I can get my array to work if I
> delete the last EEO segment in the formula out, but I cannot get it to work
> with it. Do I have too many segments for it to work? Below is my formula:
> Please help. Thank you.
>
> =SUM(IF($C$4:$C$443=$H$7,IF($D$4:$D$443=$I$7,IF($G$4:$G$443=$J$7,1,0),0)))
>

I am reviewing the websites you provided, which I really appreciate. The
formula works great! Thank you so very much!


> Unless you have Excel 2007 with SUMIFS you need to use SUMPRODUCT
>
> =SUMPRODUCT(--($C$4:$C$443=$H$7),--($D$4:$D$443=$I$7),--($G$4:$G$443=$J$7))
>
> Will tell you how may records have C-cell = H7 and D-cell =I7 and G-cell =
> J7
> For more details see
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> http://mcgimpsey.com/excel/formulae/doubleneg.html
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVP
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Shazam" <(E-Mail Removed)> wrote in message
> news:A7006BD1-C907-43AE-9AED-(E-Mail Removed)...
> >I have an EEO spreadsheet that I need to put an array formula for Sex,
> >Race,
> > & EEO category. I have Sex in the "C" column, Race in the "D" column, and
> > EEO in the "G" column, and "H7" for the Male gender match, "I7" for "01"
> > Race
> > code match, and EEO category 01 is in "J7". I can get my array to work if
> > I
> > delete the last EEO segment in the formula out, but I cannot get it to
> > work
> > with it. Do I have too many segments for it to work? Below is my
> > formula:
> > Please help. Thank you.
> >
> > =SUM(IF($C$4:$C$443=$H$7,IF($D$4:$D$443=$I$7,IF($G$4:$G$443=$J$7,1,0),0)))
> >
>
>
>

Thank you so very much, the formula works great!



"Toppers" wrote:

> try:
>
> =SUMPRODUCT(--($C$4:$C$443=$H$7),--($D$4:$D$443=$I$7),--($G$4:$G$443=$J$7))
>
>
> "Shazam" wrote:
>
> > I have an EEO spreadsheet that I need to put an array formula for Sex, Race,
> > & EEO category. I have Sex in the "C" column, Race in the "D" column, and
> > EEO in the "G" column, and "H7" for the Male gender match, "I7" for "01" Race
> > code match, and EEO category 01 is in "J7". I can get my array to work if I
> > delete the last EEO segment in the formula out, but I cannot get it to work
> > with it. Do I have too many segments for it to work? Below is my formula:
> > Please help. Thank you.
> >
> > =SUM(IF($C$4:$C$443=$H$7,IF($D$4:$D$443=$I$7,IF($G$4:$G$443=$J$7,1,0),0)))
> >