Array formulas - absolute values don't work?!

[ricci]

Hoping someone might be able to help me! I'm new to array formulas, I got the formula below working.

=SUM((Data!C2:C727='Training & PD Analysis'!A9)*(Data!B2:B727='Training & PD Analysis'!J5)*Data!E2:E727)

I want to copy the formula down 100 rows. As usual the ranges change as you move the formula down, but I want the ranges to stay the same - so I've tried using absolute values, and the calc doesn't work and I get zero

this is the formula that doesn't work:
=SUM((Data!$C$2:$C$727='Training & PD Analysis'!A9)*(Data!$B$2:$B$727='Training & PD Analysis'!$J$5)*Data!$E$2:$E$727)

Pulling my hair out with this, so i'd be very happy for any pointers! Thanks !!



<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 width=64 x:num></TD></TR></TBODY></TABLE>

 

[tclark8]

Should

'Training & PD Analysis'!A9

be locked also?

 

[Andrew Poulsom]

Welcome to the Board.

Your formula should work provided that you remembered to press Ctrl+Shift+Enter. Try this non-array formula:

=SUMPRODUCT(--(Data!$C$2:$C$727='Training & PD Analysis'!A9),--(Data!$B$2:$B$727='Training & PD Analysis'!$J$5),Data!$E$2:$E$727)

COPIED DOWN.

 

[ricci]

hi there, No I want 'Training & PD Analysis'!A9 to change as the formula is dragged down, as it's refering to an employee number that changes in each row.

 

[ricci]

Welcome to the Board.

Your formula should work provided that you remembered to press Ctrl+Shift+Enter. Try this non-array formula:

=SUMPRODUCT(--(Data!$C$2:$C$727='Training & PD Analysis'!A9),--(Data!$B$2:$B$727='Training & PD Analysis'!$J$5),Data!$E$2:$E$727)

COPIED DOWN.

Wicked that works a treat! I had tried SUMPRODUCT, but hasn't quite got it right.

Thanks ever so!