SumProduct and index formula not picking up the correct entry

agwalsh
agwalsh used Ask the Experts™
on
I have a file (constructed with help from the great people here!) which I want to use to track people who have retired. I am attaching a sample file. Starting in L12, RES has been entered to show resigned. That  number (1) should be picked up in cell BR6 (which is built on a table starting in DV10. I can see it has identified this RES entry as true (DW12) but it's not picking that up in BR6. What am I missing? Thank you
EE_Res_calculation.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I think your formula in cell BR4 should be:
=IFERROR(SUMIFS($F$11:$F$200,L$11:L$200,$BQ4),0)

Open in new window

This formula may be copied down and across.

FWIW, the formula in cell BR6 was an entirely different kind of formula. That may be why it was failing to pick up your data.
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you really want to stick with your SUMPRODUCT formula in cell BR6, then make sure the dates in DW11:FW11 match those in L1:BL1 and BR3:DR3. They were off by a year and a day.

Author

Commented:
Thank you, As usual it took another pair of eyes to see what was staring me in the face :-) Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial