Array Formula Help

[JimLes]

I am having trouble with an array formula to look for specific instances across 2 columns of data but also look in another column to see if it meets one of 5 criteria. I have tried the following formulas but they don't seem to be working:

Formula 1 using OR statement
=SUM(IF(OR('Sheet 1'!$J$1:$J$700="00983",'Sheet 1'!$J$1:$J$700="01400",'Sheet 1'!$J$1:$J$700="02577",'Sheet 1'!$J$1:$J$700="02732",'Sheet 1'!$J$1:$J$700="02919"),IF('Sheet 1'!$B$1:$B$700=$C$5,IF('Sheet 1'!$G$1:$G$700=$A10,1,0))))

Formula 2 adding instances
=SUM(IF('Sheet 1'!$J$1:$J$700="00983",IF('Sheet 1'!$B$1:$B$700=$C$5,IF('Sheet 1'!$G$1:$G$700=$A8,1,0)))+SUM(IF('Sheet 1'!$J$1:$J$700="01400",IF('Sheet 1'!$B$1:$B$700=$C$5,IF('Sheet 1'!$G$1:$G$700=$A8,1,0))))+SUM(IF('Active HC Data'!$J$1:$J$700="02577",IF('Sheet 1'!$B$1:$B$700=$C$5,IF('Sheet 1'!$G$1:$G$700=$A8,1,0)))+SUM(IF('Sheet 1'!$J$1:$J$700="02732",IF('Sheet 1'!$B$1:$B$700=$C$5,IF('Sheet 1'!$G$1:$G$700=$A8,1,0)))+SUM(IF('Sheet 1'!$J$1:$J$700="02919",IF('Sheet 1'!$B$1:$B$700=$C$5,IF('Sheet 1'!$G$1:$G$700=$A8,1,0)))))))

I am trying to determine if the first column meets "BR HQ", the second column a specific cost center, and the third column one of 5 Job Codes (00983,01400,02577,02732,02919)

Sample Set: The formula should return a count of 2.

Column 1 Column 2 Column 3
BR HQ 54128 00541
BR HQ 54128 00983
BR HQ 54128 02577

Any help would be greatly appreciated!!
Thanks

 

[SkipVought]

hi,

I am using named ranges, using the headings in your example...
[tt]
A B C D E F G H
983 1400 2577 2732 2919 SUM
BR HQ 54128 1 0 1 0 0 2
=SUMPRODUCT((Column_1=$A2)*(Column_2=$B2)*(Column_3=C$1))
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[JimLes]

Hey, thanks for the info. It appears you use helper columns to identify the Job Codes and sum them using an array. I was hoping to do it all in one formula without manipulating the data.

Is that possible? I am not sure if an array can do that.

Thanks,

 

[SkipVought]

Well you could ADD all 5 formulas.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[JimLes]

Skip,

Thanks the SUMPRODUCT did the trick all in one formula. I added all 5 Job Codes as you stated and it worked like a charm.

Thanks again!!