Conditional formatting with sumifs

[Yulyo]

Hello all,

So i came up with a formula that i must use in conditional formatting, but i just can't make it work. What am i missing?

This is the normal formula that is working if used normally (not in conditional formatting):
=IF(AND(YEAR('Sheet2'!A16)=YEAR(TODAY()); SUMIFS(Table2[Delivery
plan];Table2[Date];"<="&'Sheet2'!$A$16)='Sheet2'!$D$16); "OK"; "NOTOK")

What this formula does, is this:
- if the year in cell A16 from Sheet2 is in this year
and
sum from column "Delivery plan" is identical with cell D16 from Sheet2,
THEN
fill cell with green

Any help please?

 

[Fluff]

You cannot use table references in CF formula, you will need to change it to normal range references.

 

[CA_Punit]

Why have you used ; instead of , in the formula???

 

[Fluff]

Separators in formula vary depending on locale.

 

[Fluff]

@Yulyo
Another option is you create two named ranges, one for each of the table columns & use then in the formula.

 

[CA_Punit]

Separators in formula vary depending on locale.

@Fluff Ok Thanks

 

[Yulyo]

You cannot use table references in CF formula, you will need to change it to normal range references.

So i changed the table references and the resulted formula should be:
=IF(AND(YEAR(Sheet2'!A16)=YEAR(TODAY()); SUMIFS($J$6:$J$2201;$B$6:$B$2201;"<="&'Sheet2'!$A$16)='Sheet2'!$D$16))

Paste it like this in CF, choosed the colour, but it's not working....

 

[Yulyo]

@Yulyo
Another option is you create two named ranges, one for each of the table columns & use then in the formula.

I will try with name ranges too.

 

[Fluff]

You don't need the If statement in CF, try
=AND(YEAR(Sheet2'!A16)=YEAR(TODAY()); SUMIFS($J$6:$J$2201;$B$6:$B$2201;"<="&'Sheet2'!$A$16)='Sheet2'!$D$16)

 

[Yulyo]

I recieved an error when applied the CF: "There is a problem with the formula"

Edit: it's working

Thank you all for your help!