Excel Select particular date range for count

[sunilkeswani]

I have an excel dump with data for multiple dates in it.

Like this

02/08/05 20 30 40
02/08/05 30 50 90
02/08/05 50 60 70
03/08/05 99 50 36
10/08/05 10 10 01

How do I ask a cell to only select records for today's or yesterday's
date and only display count for the number of 20's in all 3 columns of
all records made in today's/yesterday's date ?

Please help with this.

Thanks

 

[Art Farrell]

Hi,

I assume your dates are mm,dd,yy and you want the sum of the 20's where a
figure such as 50 is two 20's. For the example you showed then:

=SUMPRODUCT((A2:A6=DATEVALUE("02/08/05"))*(INT((B26)/20)))

gives a value of 19.

For today and yesterday use:

=SUMPRODUCT((A2:A6=TODAY())*(INT((B26)/20)))+SUMPRODUCT((A2:A6=TODAY()-1)*
(INT((B26)/20)))

If you just want the actual figure 20 then replace the integer formula with
the range equal to 20.

CHORDially,
Art Farrell