I have a though one I've been trying to figure out, any help would be most
appreciated.

Spreadsheet is set up like this..

Columns
A B C D E F
1 01/01 01/02 01/03 01/04 01/05
2 01/01 100 101 102 103 104
3 01/02 100 102 103 104 105
4 01/03 200 201 202 203 204
5 01/04 300 301 302 303 304

In a cell I want it to calculate the sum for a date range (using relative
dates) but only if a column equals today's date

So for instance, in the first cell I'm looking to do this: if today was
01/03 it would first look for "today()" (in row 1), then it would calculate
the sum of the first day of the month (from column A), through yesterday
(from column A) giving me a value of 102+103=205.

Then in another cell if today was 01/03 (in row 1) it would calculate the
sum of today (from column A) through the end of the month (from column A,
assuming the spreadsheet continued to 01/31) But using the example above it
would be 203+303=506.