#VALUE - wrong data type

I'm getting an error in one cell when the same formula works in all the other ones. I have tried clearing all the empty cells and making sure all are formatted correctly but it still give me this error! Help please

The formula needs to be entered as an array with CTRL + SHIFT + ENTER
And to do that, you'll need to unmerge the cells AZ35:BA35

Thank you for your help. That did get rid of the error. Why did it work in all the other cells and not this one? Just trying to figure things out please.

Also, when I do this the total isn't correct. It is -3.082 from what it should be.

A2 is the begining of the current year as it is a rolling 12 month calendar for points. I am trying to get a current total of the PTO earned up to the current date.

As xlnetwit explained, in all the others, the initial IF evaluated to TRUE
IN P35 for example
IF(TODAY()>LOOKUP(2,1/(M2:M32<>""),M2:M32)
This evaluated to TRUE, so it calucated this
(SUMPRODUCT(--(WEEKDAY(M2:M32,2)<=5))-COUNTIFS(Q1:Q32,-8)-COUNTIFS(Q2:Q32,"H"))*8*0.0962
That part is fine, and does not require CTRL+SHIFT+ENTER

However, in AZ35
IF(TODAY()>LOOKUP(2,1/(AW2:AW32<>""),AW2:AW32)
That evaluated to FALSE, So it's calculating this instead
IF(YEAR(AW2:AW32)<=$A$2,(SUMPRODUCT((WEEKDAY(AW2:AW32,2)<=5)*(AW2:AW32<=TODAY()))-COUNTIFS(BA2:BA32,-8)-COUNTIFS(BA2:BA32,"H"))*8*0.0962

This new IF(YEAR(AW2:AW32) is the part that requres CTRL+SHIFT+ENTER

Why it's wrong, I can't say because I don't follow what that calculation is intended to do.

Can you clarify what you're trying to do with that part of the formula?

I am trying to get a current total of the PTO earned up to the current date. I am really new to this and have been getting lots of help from the forum! TY!!

I noticed also that it is counting for the whole month. If I enter anything after today it changes the total which means it's not only to the current date.

Looks like it's the countifs functions that are throwing it off, because they are including the dates that are >today

Add a criteria to the countifs for AW2:AW32,"<="&TODAY()
In BOTH fomrulas AZ35 and AZ33

So then my next question is that if it works in AZ35 then why doesn't it work in Jan-Aug? This is so confusing

It's difficult to take a formula that doesn't do what you want, and 'fix' it without a detailed knowledge of exactly what you want it to do.

It may be easier to just start from scratch, forget the existing non working formula, and just explain in words what the formula is supposed to do.
Don't just say count PTO accrued.
Give detailed description of how to arrive at your desired result, referencing which cells, what criteria etc..

Take AZ33 and AZ35.
In your mind, what are the correct answers, and how exactly did you arrive at those answers?

Since you said the others are wrong now, do the same for another example column.

This sheet is supposed to keep track of the amount of points and FMLA used on a rolling 12 month period. I wanted to get a total of of the accrued pto from month to month in one cell and another cell that has the pto up to the current day starting from the 1st of the year (PTO is not on a rolling 12 month period). The PTO calculations is basically a way for me to double check a pay-stub for accuracy. I know how to do it manually but wanted it to calculate it for me.

I am new to the "more than basic" formulas and have learned much from forums, the internet and books. Most of what I have has come from that and most I can "get the idea" from but not much more than that. I know I am asking a lot to try and figure this out. I have really bitten off more than I can chew for my first spreadsheet. I really appreciate any help that I can get.