0

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.

Improve this question
2
  • Your sheet is not accessible. Give edit permission. Commented Apr 7, 2023 at 1:56
  • @Harun24hr Sorry, my mistake. I opened it to read only access. Thank you very much for trying help ! Commented Apr 7, 2023 at 7:16

1 Answer 1

Reset to default
0

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you very much, your solution is what I needed ! Can you please explain why should I need to use the LEN function?
Well its to not color-code the blank cells

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.