Count function

  • Thread starter Thread starter Courtney
  • Start date Start date
C

Courtney

I am trying to take several spreadsheets and create a
totals page. My spreadsheets have columns including an
(eng/mfg) column, a date column, and an on-time or late
column. I want the formula to look at the date, if it is
a date in april, then count teh on-times and in a seperate
column count the lates, and the eng or mfg column. But i
can't get it to work out where we look at the date and
then count only if it is a date in april or a date in may
etc.

Thanks for your help...
 
Courtney said:
I am trying to take several spreadsheets and create a
totals page. My spreadsheets have columns including an
(eng/mfg) column, a date column, and an on-time or late
column. I want the formula to look at the date, if it is
a date in april, then count teh on-times and in a seperate
column count the lates, and the eng or mfg column. But i
can't get it to work out where we look at the date and
then count only if it is a date in april or a date in may
etc.

Thanks for your help...
Click to expand...

I'd say you're pretty darn close already... try the COUNTIF function.

Dave
dvt at psu dot edu
 
so close but yet i just can't get it...

I would love to use the COUNTIF function but haven't a
clue how to say count this column only if this column says
april. i tryed looking things up and i have found the
sumproduct formula. but i can't get that to work either!
 
Courtney said:
I am trying to take several spreadsheets and create a
totals page. My spreadsheets have columns including an
(eng/mfg) column, a date column, and an on-time or late
column. I want the formula to look at the date, if it is
a date in april, then count teh on-times and in a seperate
column count the lates, and the eng or mfg column. But i
can't get it to work out where we look at the date and
then count only if it is a date in april or a date in may
etc.

Thanks for your help...
Click to expand...

If your dates were in column A and are real dates (not text), you would need
(MONTH(A1:A100)=4) to see if they were in April.

SUMPRODUCT is a versatile function for either summing or counting, along
these lines:
=SUMPRODUCT((MONTH(A1:A100)=4)*(B1:B100="x"))
will count the number of xs in column B where the month of the date in
column A is April.

=SUMPRODUCT((MONTH(A1:A100)=6)*(YEAR(A1:A100)=2003)*(B1:B100>$D$999)*(C1:C10
0))
will add the entries in column C corresponding to column A dates in June
2003 and column B greater than whatever is in cell $D$999.
 
Back
Top