conditional count

  • Thread starter Thread starter sherry
  • Start date Start date
S

sherry

i have a table with 5 columns. OL 4 contains values
ranging 0-10

COL2 contains dates (mm/dd/yyyy). Can i return the number
of 0s, 1s .. 10s with a specified date range?
 
Hi Sherry!

Try:

=SUMPRODUCT((B1:B33>=DATE(2003,12,23))*(B1:B33<=DATE(2004,1,5))*(D1:D3
3=1))

You might find it better to put the two dates and the col D criteria
in separate cells.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Sherry!

Thanks for thanks. Just a note that if you are converting the criteria
and intend to copy down, make sure that you convert the range
references to absolute.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
hi norman,

i took your advice and used cells instead of constants
within the formula. yeah i've made them absolute! WORKS
LIKE A CHARM - u r GOOD!

- sherry
 
Hi Sherry!

Thanks for thanks is always appreciated and confirms to Google
Searchers etc. that something works.

Constants in cells rather than formulas are usually better as they
allow you to vary your formula without editing it. But in your case it
can also aid in the construction of tables.

I think it's probably best practice in any event to use:

Example:
=SUMPRODUCT(($B$1:$B$33>=DATE(2003,12,23))*($B$1:$B$33<=DATE(2004,1,5)
)*($D$1:$D$33=H1))

It's probably quite rare to want the ranges being studied to vary when
copying down or across.

This way I can set up H1:H11 with the potential inputs 0-11 and with
the formula in I1 I can then copy down and get the counts for all 11
inputs.

Similarly, careful referencing of cells containing the start and end
dates can allow easy construction of tables showing numbers of each
variable for different pairs of dates.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top