Index match when the condition is true

[AKuma0411]

Hi there,

Can someone help me build a logic to accomplish below requirement:

There are two tabs in excel wb: Sheet A and Sheet B as shown in images

I want a formula in Sheet A(column E), which will look for set# 01.111.120 in sheet B (column A) then It should check the condition in (column G) Rec. total equipment >0 for all occurrences, which is "yes" in this example, because out of 3 instances for this set #, for one instance it's 2, then it should return Total procedures from Col C (sheet B),which is 8 in this case 

[#2]

@AKuma0411 

Does the attached sample file return the intended result? The formula works with IF and SUMIFS instead of INDEX and MATCH.

[#3]

No, I just highlighted 8 and 10 in this case because that's the value the formula should return. Can you please share the logic which can help to get the result? Thanks!

[#4]

@AKuma0411 

You are welcome. Unfortunately it seem that i don't understand the logic of the task. Perhaps an expert of the community can help you with this.

[#5 · Accepted solution]

Hello,
I kind of answered my own question, this formula worked as per my expectation
=IFERROR(SUMIFS('Sheet B'!$C:$C, 'Sheet B'!$A:$A, 'Sheet A'!$B2, 'Sheet B'!$G:$G, ">0"), "")

Thanks!