Lookup and Count

[H.Schurch]

Dear Experts,

Looking for some help

I have a list box in cell B3 from where I pick a date that is in the range
starting in cell A17, to A41. Each cell from A17 to A41 represent a month,
A17 = 01-Sep-03, A18= 01-Oct-03, etc

For each month 28 entries, a number from 0 to 10 has to be entered into the
adjacent cells, one entry per cell. If 01-September-03 is in A17 the entries
would span from B17 to AC17. The same would apply for 01October-03 in row
18. If I select a month from the list in B3 I need to count how many entries
in that month were between

1-5

6

7

8

9

10

and display each total in cells J3 to J8.

What formula would I have to use? Can't worke it out with VLOOKUP.

Any Help is much appreciated

Regards

Aussie

 

[Bob Phillips]

Aussie,

In J3 use

=SUMPRODUCT((A17:A41=DATE(2003,9,1))*(B17:AC41>=1)*(B17:AC41<=5))

in J4

=SUMPRODUCT((A17:A41=DATE(2003,10,1))*(B17:AC41=6))
J5-J* similar to J4.

Just substitute the date that I have input for the cell where you store the
selected date.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)