PDA

View Full Version : Solved: Counting Occurrences by Date and Time



willhh3
05-25-2011, 01:45 PM
I’m guessing this is an easy one, but I can’t seem to get it right. I have columns that show the number of times a call is received by date and time. I want to calculate the number of calls that come in on Sunday at 1pm, 2pm, etc. and then repeat for Monday – Saturday. I’ve been playing with =sumproduct, =sumif, and =countifs to no avail. I've also been trying to use the "Weekday = 1 (for Sunday) function in my formulas - to translate the dates into day of the week.

Sample spreadsheet attached and thanks in advance.

Whh3

Bob Phillips
05-25-2011, 03:13 PM
A sample spreadsheet is only of use if it has something in it.

But maybe something like

=SUMPRODUCT(--(WEEKDAY($A$2:$A$200)=1)) for Sunday,

or even

=SUMPRODUCT(--(TEXT($A$2:$A$200,"ddd")="Sun"))

willhh3
05-25-2011, 04:03 PM
Thanks for the quick response.

Hmmm, odd that my sample sheet is empty. My copy has data. Let me try to post again. You first formula is close to what i was playing with. I need to tally column B for the values found in A...does that make sense?

Bob Phillips
05-25-2011, 09:07 PM
Use

=SUMPRODUCT(--(TEXT($A$3:$A$11,"dddd")=$E3),B$3:B$11)

willhh3
05-26-2011, 09:31 AM
Thank you xld! The fomula worked perfectly. I guess I was going down the worng path with "Weekday=1."

Regards
Whh3

Bob Phillips
05-26-2011, 01:43 PM
No that would work, I just used the data you had setup in the table.