What's wrong with this formula (i.e. returning FALSE values)?

[BoGuSforLife]

=IF(AND('Available ATO Hours'!A:A>=(TODAY()-15),'Available ATO Hours'!A:A<=(TODAY()+15)),SUMIF('Available ATO Hours'!F:F,'ATO Usage'!B:B,'Available ATO Hours'!G:G),0)

For reference:
'Available ATO Hours'!A:A = Various dates
'Available ATO Hours'!F:F = Various names
'ATO Usage'!B:B = Names that can be found in 'Available ATO Hours'!F:F
'Available ATO Hours'!G:G = Various numerical values

Please keep in mind that I am relatively inexperienced with Excel but I have the exact same formula I made in a test sheet before I had the actual data and it works just fine. The only difference is that the data is in different columns but my logical tests are the same.

Any help would be greatly appreciated.

 

[BoGuSforLife]

Looks like some of the quotes are in the wrong places.

Try this...

=SUMIFS('Available ATO Hours'!G:G,'Available ATO Hours'!A:A,">="&'ATO Usage'!I1,'Available ATO Hours'!A:A,"<="&'ATO Usage'!J1,'Available ATO Hours'!F:F,'ATO Usage'!B2)

This did the trick. I had tried a SUMIFS originally but I was missing the "&" symbols and was unsure about the quotations as well. Would you be able to provide any insight into the proper syntax as far as when to use the "&" symbols? Also, should all criteria be enclosed by quotation marks regardless?

 

[T. Valko]

This did the trick. I had tried a SUMIFS originally but I was missing the "&" symbols and was unsure about the quotations as well. Would you be able to provide any insight into the proper syntax as far as when to use the "&" symbols? Also, should all criteria be enclosed by quotation marks regardless?

Some examples...

Use the concatenation operator when using comparison operators with cell references or other functions:

=SUMIF(A1:A10,">="&D1,B1:B10)
=SUMIF(A1:A10,"<>"&D1,B1:B10)

=SUMIF(A1:A10,">="&TODAY(),B1:B10)
=SUMIF(A1:A10,"<"&DATE(2011,1,15),B1:B10)
=SUMIF(A1:A10,">"&MAX(D1:D5),B1:B10)

Use quotes on the comparison operators (as above) and any hardcoded text:

=SUMIF(A1:A10,"Tom",B1:B10)
=SUMIF(A1:A10,"",B1:B10)
=SUMIF(A1:A10,"<>North",B1:B10)
=SUMIF(A1:A10,"*",B1:B10)

That last example is using the asterisk wildcard which, in this application, means ANY text.

Do not quote numbers.

=SUMIF(A1:A10,10,B1:B10)

 

[BoGuSforLife]

Some examples...

Use the concatenation operator when using comparison operators with cell references or other functions:

=SUMIF(A1:A10,">="&D1,B1:B10)
=SUMIF(A1:A10,"<>"&D1,B1:B10)

=SUMIF(A1:A10,">="&TODAY(),B1:B10)
=SUMIF(A1:A10,"<"&DATE(2011,1,15),B1:B10)
=SUMIF(A1:A10,">"&MAX(D1:D5),B1:B10)

Use quotes on the comparison operators (as above) and any hardcoded text:

=SUMIF(A1:A10,"Tom",B1:B10)
=SUMIF(A1:A10,"",B1:B10)
=SUMIF(A1:A10,"<>North",B1:B10)
=SUMIF(A1:A10,"*",B1:B10)

That last example is using the asterisk wildcard which, in this application, means ANY text.

Do not quote numbers.

=SUMIF(A1:A10,10,B1:B10)

Thank you so much for all of your assistance. You truly went above and beyond.

 

[T. Valko]

Thank you so much for all of your assistance. You truly went above and beyond.

You're welcome. Thanks for the feedback!