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?

Improve this question
2

1 Answer 1

Reset to default
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.

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .