lukesanborn,
I'm guessing the reason your initial formula is returning #N/A error is because it is an ARRAY formula, but you applied it by hitting Enter, instead of Control + Shift + Enter... also, the original formula can be shortened to:
For cell R15:
To apply the array formula:
Select cell R15, clear out the contents in the formula bar, paste the above formula into it and hit Ctrl + Shift + Enter and it should work.
HOWEVER... it is an ARRAY formula. I would recommend going with the SUMPRODUCT formula I posted earlier (non-array) as it will process faster and use far less resources given the # of cells that formula will be inserted into!
Also...
You missed an opening parenthesis just after the MATCH(1,(.... as well as a closing parenthesis at the end.
You also didn't create row/column absolute references for your match columns. In your original formula, A15 should be $A15, and R1 should be R$1. Doing so will enable you to copy/fill that cell in any direction and have the formula adjust correctly.
- Moo
Bookmarks