DSUM & DAVERAGE 2

[lespaul]

I have the following information in a spreadsheet:

Date       Day      # scheduled     # initialized
7/1/2004   Thursday      7                0
7/2/2004   Friday        3                1
7/5/2004   Monday        0                0
7/6/2004   Tuesday       5                1
7/7/2004   Wednesday     7                0
7/8/2004   Thursday      7                1
7/9/2004   Friday        1                0
7/12/2004  Monday        4                0
7/13/2004  Tuesday       5                0
7/14/2004  Wednesday     4                0
7/15/2004  Thursday      5                0
7/16/2004  Friday        0                0

I have named the range "Database".

I want to end up with:

Total # on Mondays Monday Average
Total # on Tuesdays Tuesday Average

etc.

from this sample I would expect the following results:

Day                   # scheduled        Average
Monday                      4                2
Tuesday                    10                5
Wednesday                  11                5
Thursday                   19                6
Friday                      4                1

I've tried setting up my criteria in different ways, but all I can get is a total count and average, not grouped by the day!!

Thanks for any help!!

les

 

[pbrodsky]

look up the functions SUMIF(), and COUNTIF() in the Help file. Those should do it for you easily.

 

[lespaul]

Ok, I used:

=SUMIF(B2:B178,Monday, C2:C178)

I tried:

=SUMIF(B2:B178,"Monday", C2:C178)

and

=SUMIF(B2:B178,"=Monday", C2:C178)

and they all returned 0 (zero)

I think it has to do with the fact that the day is a formula.

I have two queries, one contains the scheduled info and the other contains the initialized info. This information in on worksheet two. Each query returns dates and the corresponding numbers.

Then on worksheet one I added all the dates 7/1/2004 - 3/4/2005. I set column b to be formated "ddd" equal to the date in column 1:

column1      column2 (formatted "dddd")
7/1/2004     =A2 (value = Thursday)
7/2/2004     =A3 (value = Friday)

Could the problem be that it's a "formatted date"? How do I get around that?


Leslie

 

[bluedragon2]

=SUMPRODUCT((B2:B178="Monday")*(C2:C178))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[pbrodsky]

sumif works for me.

formula is
=SUMIF($B$3:$B$15,B3,$C$3:$C$15)

in column 4
if col 2 has Monday etc.
and col 3 has amounts to total.

 

[Zathras]

Why not just use a pivot table?
This just took a few seconds:
[tt]
Data
Day Sum of scheduled Average of scheduled
Monday 4 2
Tuesday 10 5
Wednesday 11 5.5
Thursday 19 6.333333333
Friday 4 1.333333333
Grand Total 48 4
[/tt]

 

[lespaul]

BlueDragon, that returned zero too!

any other suggestions?

Leslie

 

[GlennUK]

I would use a PivotTable as per Zathras' suggestion, but, if you want to summarise using formulae, then use the SUMPRODUCT function with the WEEKDAY function ... like this:

=SUMPRODUCT((WEEKDAY(B2:B13)=3)*C2:C13)

the comparison "=3" matches Tuesday.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[lespaul]

I tried the pivot table first and it was all screwy, which is why I was considering the database functions!!

The pivot table won't group by day. I get all the days and it looks just like it does:

thurs
friday
monday
tuesday
wednesday
thursday
friday
monday
tuesday
....

Leslie

 

[Zathras]

If you still want to use database formulae, then you can set it up like this:
[tt]
Monday =DSUM(DATABASE,3,$G$1:$G$2) =DAVERAGE(DATABASE,3,$G$1:$G$2)
Tuesday =DSUM(DATABASE,3,$H$1:$H$2) =DAVERAGE(DATABASE,3,$H$1:$H$2)
Wednesday =DSUM(DATABASE,3,$I$1:$I$2) =DAVERAGE(DATABASE,3,$I$1:$I$2)
Thursday =DSUM(DATABASE,3,$J$1:$J$2) =DAVERAGE(DATABASE,3,$J$1:$J$2)
Friday =DSUM(DATABASE,3,$K$1:$K$2) =DAVERAGE(DATABASE,3,$K$1:$K$2)
[/tt]
Note that each day uses a separate criteria range. For example, here is G1:G2 (Monday)
[tt]
G1: Day
G2: Monday
[/tt]

If you want to set it up with only a single criteria range, that can be accomplished by using a 1-way data table. But with only 5 criteria ranges to worry about, it is easier to do it this way.

 

[Zathras]

Aha! I see the problem. Formatting a date to look like "Monday" does nothing to the cell contents. You need to use formulas like this in column "B"
[tt]
=TEXT(A2,"dddd")
[/tt]
Put that in B2 and copy down. Then all should become clear.

 

[lespaul]

thank you!!!! I knew it had to be the formatting!!!

you rock!!

les


Leslie