sum certain days in a month

[robcarr]

Hi,

I am trying to calculate a specific day of the week in a range to get a weighted average.

i think it is similar to this, but cant get it work it shows #value

SUM((DAY($C$1:$AT$1)=DAY("1 "&AW$3))*($C$1:$AT$1<>0)*OFFSET($C4:$AT4,-1,0))

aw3 has this formula in

=IF(WEEKDAY($D$2, 2)>0, 7-WEEKDAY($D$2, 2), 0)+$D$2

d2 has the first day of the month in

range d2 to at2 has the dates in the month with 2 weekly totals after the saturday in each week, the weeks go sunday to saturday. row 4 has the values I want to sum for each day,

days are setup in columns aw to bc with aw being sunday and bc being Saturday

hope this is all that is needed.

Hope this is of use, Rob.

 

[GlennUK]

Can you explain in detail what it is that you are trying to do.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Hi,

TEXT(YourDateRef,"ddd") is a day of the week.

You will want to use SUMIF, if you have ONE criteria or SUMPRODUCT if there are multiple criteria.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

i am trying to calculate all days of a week individually

so i need to know the total of all monday's, tuesday's etc.



Hope this is of use, Rob.

 

[SkipVought]

Add the TEXT formula to an adjacent column, and use a PivotTable to summarize by DAY.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

i cant use a pivottable it is a world wide report and cant have sheets added or extra info added, what i am trying to do is simplify a formula that is being used,

this is the formula i am trying to automate via a formula

=IF(ISERROR(AVERAGE(D4,M4,U4,AC4,AK4)*5),"0",(AVERAGE(D4,M4,U4,AC4,AK4)*5))

there is one of these for each day for each sheet(20) and approx 190 rows, the author has to send out this report monthly and has to check all the average formulas above and add or remove data if it is not required.

Hope this is of use, Rob.

 

[SkipVought]

AVERAGE?

I thought you were asking about values that, "I want to sum for each day."


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

no that is not what i said,

AVERAGE?

I thought you were asking about values that, "I want to sum for each day."

the chap that has the spreadsheet is doing it as a weighted average I wanted to get a sum of the figures then divide by the number of times say a monday appear to get an average without having to change the formulas each day.

all i am haviong problems with is getting the sum correctly calculated for each monday that appears in the data.


Hope this is of use, Rob.

 

[SkipVought]

[tt]
=SUMIF(YourDAY_Range,DAY_REF,YourValueRAnge)/COUNTIF(YourDAY_Range,DAY_REF)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

On second thought...
[tt]
=SUMIF(YourDAY_Range,DAY_REF,YourValueRAnge)/COUNTA(YourDAY_Range)

[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

this would work if the date was text, i.e Sunday

however the day row is referenced by a date, so it only ever picks up the date that it is looking for and not all the Sunday's in a month.



Hope this is of use, Rob.

 

[SkipVought]

TEXT(YourDateRef,"ddd") is a day of the week.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

when i do that it only counts the 1 day and not the other 4 on the sheet.

Hope this is of use, Rob.

 

[SkipVought]

How about posting some sample data, and an explanation of what's not working with respect to the sample.

Also your exact formula.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[robcarr]

dont worry,

i dont have time to go through it anymore, cant send sample data as it is too complex.

I will just let him do it manually.

Hope this is of use, Rob.