Subtotals from a Horizontal List

**gico1972** · 10-21-2009, 12:41 PM

The following has me scratching my head and i am anxious for any help to obatin the results that i need.

From a long list of date as shown below how do i calculate totals for each day of the week. Below is a small example of the data that i have.

I want to establish totals for each monday for example for the long list of data that i have spanning 200 days.

Aprpeciate any help that someone can give on this

08-Apr 09-Apr 10-Apr 11-Apr 12-Apr 13-Apr
0 129 123 114 0 138

**jj72uk** · 10-21-2009, 01:31 PM

Are the dates set out as above, or can it change to ddd/mm/yy?

**DonkeyOte** · 10-21-2009, 02:59 PM

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)

**gico1972** · 10-22-2009, 04:14 AM

I have attached the document to perhaps make what i am asking a little clearer.

I am trying to obtain the sum for row B for each week day.

Thanks

**DonkeyOte** · 10-22-2009, 04:30 AM

There was a typo in my prior post in so far as I meant SUMPRODUCT rather than SUMPROCUCT!

Using your file... if for ex. you wanted Mon-Sun values in HI2:HO2 then

HI2: =SUMPRODUCT(--(WEEKDAY($B$1:$HG$1,2)=COLUMNS($HI2:HI2)),$B2:$HG2)
copied across to HO2

**gico1972** · 10-22-2009, 04:41 AM

Thanks. How do i actually establish totals for all Mondays, Tuesdays, Wednesday etc?

**DonkeyOte** · 10-22-2009, 04:41 AM

Your question is not clear.... the formula provided totals all values where date is Monday - if copied across horizontally (as advised) you will get Tuesday, Wednesday etc (7 formulae for 7 days)