Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Index match when the condition is true

Brass Contributor

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

AKuma0411_2-1702581143484.png

 

AKuma0411_1-1702580840641.png

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 

4 Replies

@AKuma0411 

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

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!

@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.

best response confirmed by AKuma0411 (Brass Contributor)
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!
1 best response

Accepted Solutions
best response confirmed by AKuma0411 (Brass Contributor)
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!

View solution in original post