Look up date on another sheet and do count of active cells (column

[gary m]

I have an Excel summary sheet that retrieves daily data from departmental
sheets based on date search (eg. TODAY()-1,etc.. I need a formula that will
go to the sheet, search for the date and then "Count" the cells in columns
next to the date that have data. I have used vlookup to find specific cells
but can't seem to 'count' multiple cells/columns next to the date. As you can
see, I am somewhat an inexperienced user. Thanks for the help.

 

[Bob Phillips]

Will that date appear once or more times on the other sheet?

Maybe something like

=COUNTA(OFFSET(Sheet2!A1,MATCH(TODAY(),Sheet2!A1:A1000,0)-1,0,1,256))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[gary m]

Bob:
Date only appears once. Dates are in column b only, b8 thru b39 for the days
of the month. The sales by item for each date is next to it in columns C thru
H, so for Jul1 the date would be in b8 and the numbers would be in c8 thru
H8. Below is how structured with total of six item columns. The formula needs
to find the date and then count, in this case C8..H8 for July 1.

B C D E F G
H
Date Item 1 Item 2
7/1/06 322.00 300.00
7/2/06 312.00

Tried to apply your formula to this without success. Thanks for the help.

 

[Dav]

if you know the names of the other sheets

=COUNT(OFFSET(Sheet2!C1,MATCH(TODAY(),Sheet2!B2:B22),0,1,200))

if the other sheet is called sheet2 i have the rows may need adjusting
as may rhe 200 maximum columns that could contain values


regards

Dav

 

[Bob Phillips]

It was probably the 256 that caused the problem if you start in column B
against column a as I did, so reduce it

=COUNT(OFFSET(Sheet2!B8,MATCH(TODAY(),Sheet2!B8:B39,0)-1,0,1,255))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[gary m]

Bob:
The correction you provided re 255 columns was right, however the correct
string turned out to be -1,1,1,255 - using 1 instead of 0. Using this string
and testing it, the answers are correct. Still trying to understand whole
string so can't tell why it works yet but that is correct string. Appreciate
all the help. Thanks,
Gary

 

[Bob Phillips]

Gary,

That is because my formula counted the date as well (yes I know, that is
dumb <g>), so you need to offset the start point by 1 column, which is what
you mod did.

The OFFSET arguments are
- start cell
- number of rows to offset
- number of columns to offset
- number of rows to reference
- number of columns to reference

so the formula

=COUNT(OFFSET(Sheet2!B8,MATCH(TODAY(),Sheet2!B8:B39,0)-1,1,1,255))

starts at B8, MATCHes the date against the dates B8:B39 and uses that as the
number of rows to offset, so it effectively starts at that number of rows
past B8, offset 1 column (so as to not count the date), and then counts
within a range of 1 row and 255 columns from that point.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)