Help needed - cell reference in array formula

[ridgeway1721]

Hi all,

Just joined and would be grateful for help with the following excel formula issue that I am running into. When using an array formula, is it possible to include cell references instead of actual words or numbers? If not, what is the best work-around to make the formula more dynamic, so that it can pull from a cell reference?

The current formula I am working with is below.

{=SUM(('Employee only data'!$AO$2:$AO$40000="HR")*('Employee only data'!$AU$1:$AU$40000="9017963")*'Employee only data'!$AT$2:$AT$40000)}

I would like to replace "HR" and "9017963" with cell references (e.g., $D2 and B$3) that contain these words / values to make the equation more dynamic.

Thank you in advance for the help!

 

[mikerickson]

What happened when you tried it?

 

[ridgeway1721]

I got an #N/A sign. The problem appears to be the 9017963 number. When I stick that into the equation or reference the cell that has it, it doesn't compute. I have a number of these different 7-digit numbers, based on a column in my data set where each row is labeled with one of them (and where multiple rows have the same 7-digit number). Any thoughts on what I might be doing wrong? Is the SUM array formula able to read a number like this?

Thanks for the help!

 

[FDibbins]

This is probably because AU contains text numbers, and you entered a real number in B3

{=SUM(('Employee only data'!$AO$2:$AO$40000="HR")*('Employee only data'!$AU$1:$AU$40000="9017963")*'Employee only data'!$AT$2:$AT$40000)}
......>
{=SUM(('Employee only data'!$AO$2:$AO$40000=$D2)*('Employee only data'!$AU$1:$AU$40000=text(B$3,"0")*'Employee only data'!$AT$2:$AT$40000)}

However, I have a feeling you could do this with SUMIFS()....

=SUMIFS('Employee only data'!$AT$2:$AT$40000, 'Employee only data'!$AO$2:$AO$40000, $D2, 'Employee only data'!$AU$1:$AU$40000, $B2)
(You may still need to add the TEXT() bit)