0

enter image description here

I can't figure out the proper formula to lookup a value based on three different criteria. I want to fill out the summary table with data from the source table.

I tried using INDEX, MATCH and SUMPRODUCT but the value always returned "-" instead of the correct value. Not sure how to combine the formula together

Say I want to fill in the Summary Table D16 where PersonA matches for both tables, the data is for Quarter 2 and the data would match the type which is Expense. How do I do this?

2

Use INDEX to return the correct row:

INDEX(B:G,MATCH(A16,A:A,0),0)

Then use SUMIFS to return the correct Column's Value in that range:

=SUMIFS(INDEX(B:G,MATCH(A16,A:A,0),0),$B$2:$G$2,$C16,$B$3:$G$3,D$15)

Put the above in D16, copy over and down.

  • nice! I was running through some sort of aggregate sumproduct option. Much better to avoid the array calc option. +1 – Forward Ed Jan 9 at 13:32

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.