Sumif / Countif Problem

  • Thread starter Thread starter Rita Palazzi
  • Start date Start date
R

Rita Palazzi

Windows XP Professional
Office 2000

I have a worksheet in which daily data is collected. Each row is
indexed by the date. I need to get the average for several indices
based on the day of the week, i.e. average shipment count for all
Mondays, Tuesdays, etc. I thought about doing a "sumif" statement then
dividing by a "countif" statement, but I'm not sure how the syntax would
work for the criteria portion of these functions. Any ideas on how to
accomplish my objective??

Thanks in advance for any help you can provide!

Rita Palazzi
Senior Engineer / GTS Int'l MIS
FedEx Express
 
Let A2:A100 house th dates and B2:B100 the associated figures.

In C2 enter & copy down:

=TEXT(A2,"dddd")

In F2 enter & copy down:

=SUMIF($C$2:$C$100,E2,$B$2:$B$100)/MAX(1,COUNTIF($C$2:$C$100,E2))

where E2 houses the Monday day of the week, E3 Tuesday, etc.
 
SUMIF and COUNTIF only use a single direct criterion value, they cannot
calculate a secondary value (day of week from date) to be used for the
criteria.

That puts you into an array formula situation. Perhaps the most easily
understood array approach would be to use
=AVERAGE(IF(WEEKDAY(dateRange)=2,shipmentsRange))
array entered (Ctrl-Shift-Enter) would average shipment counts for
Mondays (Sunday=1, ... Saturday=7).

Note that this formula would consider an empty date cell to be a
Saturday, so you could make it more bulletproof by
=AVERAGE(IF(ISNUMBER(dateRange)*(WEEKDAY(dateRange)=2),shipmentsRange))
or
=AVERAGE(IF(ISNUMBER(dateRange)*ISNUMBER(shipmentsRange)*(WEEKDAY(dateRange)=2),shipmentsRange))

Jerry
 
Back
Top