SUMIFS is retaining Zero

In the Sheet "Allocation tool", it has a column "Available hours based on project dates" which has SUMIFS formula. How ever I try it retains Zero '0'

I am unable to understand if this is because the date lists are having another column reference or something else.

=SUMIFS($F8:$L8:$R8:$X8:$AD8:$AJ8:$AP8:$AV8:$BB8:$BH8:$BN8:$BT8:$BZ8:$CF8:$CL8:$CR8:$CX8:$DD8:$DJ8:$DP8:$DV8:$EB8:$EH8:$EN8:$ET8:$EZ8:$FF8:$FL8:$FR8:$FX8:$GD8,$F$6:$L$6:$R$6:$X$6:$AD$6:$AJ$6:$AP$6:$AV$6:$BB$6:$BH$6:$BN$6:$BT$6:$BZ$6:$CF$6:$CL$6:$CR$6:$CX$6:$DD$6:$DJ$6:$DP$6:$DV$6:$EB$6:$EH$6:$EN$6:$ET$6:$EZ$6:$FF$6:$FL$6:$FR$6:$FX$6:$GD$6,"=>"&$G$3,$F$6:$L$6:$R$6:$X$6:$AD$6:$AJ$6:$AP$6:$AV$6:$BB$6:$BH$6:$BN$6:$BT$6:$BZ$6:$CF$6:$CL$6:$CR$6:$CX$6:$DD$6:$DJ$6:$DP$6:$DV$6:$EB$6:$EH$6:$EN$6:$ET$6:$EZ$6:$FF$6:$FL$6:$FR$6:$FX$6:$GD$6,"<="&$H$3)

I all attach the sheet for reference

Dates in F6 etc for "Available Hours" are TEXT not Dates no will never match G3 & H3

Alternative formula

=SUMPRODUCT(($F8:$GI8)*($F$7:$GI$7="Available hours")*($F$6:$GI$6>=$G$3)*($F$6:$G$I6<=$H$3))

You will get errors because the dates in row 1 of "Associate Skills" are TEXT not proper dates so they never match the dates in G3 and H3.

the dates need to be changed to proper dates.

($F$7:$GI$7="Available hours") simply selects columns which have headers of "Available Hours".

It will work but only when dates in "Associate Skills" are changed to dates. You need to put the dates in row 2 Move everything down) as Tables appear to row 1 as text headers (I don't like Tables!).

I assume you simply changed to headers in "Associate Skills" from "Column 10", "column 11" etc with dates e.g 01-apr-2021. Hence these dates are simply text

So (as far as I understand tables) you need to remove the table header row and replace it with your own so columns A:L will be as current header row and M onwards will be proper dates. Obviously this will impact on some of your formulae

As I understand, you mean to say that under "Associate_Skills" sheet, I will need to have columns with dates (from M till the end) removed from table and just be range(not like a table). Then make sure they are dates by right clicking and format to dates. And Yes, it will change a lot of formulas in "Allocation tool" since I have vlookup for values which I will have to do if this is the only way. Also, I need to change columns like F6 in "Allocation tool" which has values like "=Associate_Skills!M1" and just need to right click and change to dates format. This is my understanding, correct me if I am wrong and I will try doing this

Yes, unfortunately you need to have your dates as dates so any comparisons will work.

NOTE: using SUMPRODUCT, if you need, for example, to sum "Occupied Hours" you will need the date in column G etc.

Good to hear: well done!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Sure, I have added the reputation

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Can you please do the above - thank you.