| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 Array Formula
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))) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
3 Array Formula
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" wrote in message ... 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))) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
3 Array Formula
I would think it very likely that you'd do better with a Pivot Table:
http://peltiertech.com/Excel/Pivots/pivotstart.htm In article , 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))) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
3 Array Formula
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))) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
3 Array Formula
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" wrote in message ... 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))) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
3 Array Formula
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))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| array formula | Excel Worksheet Functions | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| Array Formula? | Excel Worksheet Functions | |||
| Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
| Help with array formula | Excel Worksheet Functions | |||
Linear Mode
