adding countifs, both with multiple values possible

[larryvoorhees]

I'm building an Absence Calendar, and attempting to accumulate the number of "occurrences" for each individual.
Each agent has two rows. Top row is blank, or has the value "UP","T" or "LE" in it. Second row for each agent is either blank, or has a numeric value from 0 - 8 (number of hours gone) in it.
I want to accumulate in the following manner: If Row 1 is "UP", "T" or "LE" and bottom row is a value of 2 or greater, count 1. If Row 1 is "UP", "T", or "LE", and bottom row is >=.25 and <2, count .5.
I just can't seem to get it right without double-counting.
Here is the formula I have so far, but I know it's not correct:
=(COUNTIFS(C94:AG94,{"UP";"T";"LE"},C95:AG95,">2")+(COUNTIFS(C94:AG94,{"UP";"T";"LE"},C95:AG95,">=0.25",C95:AG95,"<=2")*0.5))

With the formula above, I don't believe it's counting the items as "OR", as in "if the value is UP or T or LE" AND >2, etc.

Any help anyone can provide would be greatly appreciated. This has killed way too many hours and brain cells already! Thanks!

 

[mrhstn]

I usually wrap the COUNTIF(S) in a SUMPRODUCT.

=SUMPRODUCT(COUNTIFS(A1:A7, {"UP","LE","T"}, B1:B7, ">2"))....

 

[larryvoorhees]

I shall give it a go. Thank you, sir.

 

[larryvoorhees]

=SUMPRODUCT(COUNTIFS(C94:AG94,{"UP";"T";"LE"},C95:AG95,">2")+(COUNTIFS(C94:AG94,{"UP";"T";"LE"},C95:AG95,">=0.25",C95:AG95,"<=2")*0.5))

worked like a charm. Thank you!

 

[mrhstn]

You are very welcome