CODEWITHSUNDEEP
google-sheetsconditional-formattingsumifs
JustAnotherDeveloper
JustAnotherDeveloper

Reputation: 3

How use SUMIFS based on another sheet in conditional formatting?

I have google sheets with 2 sheets and I would like to use conditional formatting with SUMIFS based on the second sheet. Unfortunately, I didn't find the way to do it

Link for Google Sheet, I would like to mark in Green the column D in Sheet2 if the value is equal to the SUMIFS result

** I'm searching solution without using the Column E in Sheet2

I'll appreciate any help here

I was trying use the SUMIFS in the conditional formatting without result. Tried also FILTER(Sheet1!D:D, Sheet1!A:A=A2,Sheet1!B:B=B2, Sheet1!C:C=C2) and still nothing

Using MATCH function isn't suitable to my needs because I need to copy the conditional formatting to all rows. MATCH will cause I need to edit each row formula.

Upvotes: 0

Views: 311

Answers (1)

rockinfreakshow
rockinfreakshow

Reputation: 29982

You may try:

  • Apply to range: D2:D
  • custom formula:

=len(D2)*(D2=SUMIFS(INDIRECT("Sheet1!D:D"), INDIRECT("Sheet1!A:A"), A2, INDIRECT("Sheet1!B:B"), B2, INDIRECT("Sheet1!C:C"), C2))

  • also remove unused formatting rules(related to this scenario) present in the tab

enter image description here

Upvotes: 0

Related Questions