Conditional formatting based on rows AND columns

Yes, that is correct! /Helena

This proposal uses helper columns that reference a small lookup table. In M1:N2 find the lookup table which assigns numeric values of 1 to Full time and 0.75 to Part time.

Then for the helper columns array enter this formula in F2 fill across and down as far as needed.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

=SUM(IF(($A2=$A$2:$A$10)*TRANSPOSE(C2=$M$1:$M$2),SUMIF($M$1:$M$2,C$2:C$10,$N$1:$N$2)))

The formula for Red in CF

Formula:

=AND(C2<>"",F2>=2)

and for Green

Formula:

=AND(C2<>"",F2<2)

Applied in the attached.

I'll have to think about that. I am thinking along the lines of SUMPRODUCT or MMULT.

In the meantime this would dispense with the helper columns, but still require the lookup table M1:N2.

In Name Manager this named formula Full_Part

Formula:

=SUM(IF((Sheet1!$A2=Sheet1!$A$2:$A$10)*TRANSPOSE(Sheet1!C2=Sheet1!$M$1:$M$2),SUMIF(Sheet1!$M$1:$M$2,Sheet1!C$2:C$10,Sheet1!$N$1:$N$2)))

Then the CF rules become Red:

Formula:

=AND(C2<>"",Full_Part>=2)

Green:

Formula:

=AND(C2<>"",Full_Part<2)

I applied these conditional formatting formulas to the range C2:E100

=AND(C2<>"",OR(COUNTIFS(C$2:C$100,"Full time",$A$2:$A$100,$A2)>1,COUNTIFS(C$2:C$100,"*time",$A$2:$A$100,$A2)>2))

red format

=SEARCH("Time",C2)

green format

See attached - try changing some of the values to see if it works for you

For more columns the formulas can remain exactly the same - just change the "applies to" range in conditional formatting

This is simpler than my previous in post #6 and no longer requires helper columns or lookup table.

In the Name Manager Full_Part formula try

Formula:

=SUMPRODUCT((Sheet1!$A2=Sheet1!$A$2:$A$10)*(Sheet1!C$2:C$10={"Full time","Part time"})*{1,0.75})

The CF formulas remain the same as before.

No tips. Just a thought.

What is the range in "Applies to:"?

Applied to excessively large ranges ... especially whole columns ... will slow performance.

Sorry, here is the worksheet.
BR,
Helena
Edit: Sorry again, mistakenly attached file with original information.