A sample file will help as it's not clear exactly how your data is laid out...

If you have multiple rows to sum, say rows 2:100 and dates are in row 1 then one approach might be

=SUMPROCUCT((WEEKDAY($A$1:$GR$1)=1)*($A$2:$GR$100))
would for ex. sum all values for Sunday
(assumes all values in A2:GR100 to be either numeric or blank)