Re: Index & Match

We need to find a criteria that is common to the specific columns where
your data is located.

Actually, that should read:

We need to find a criteria that is common and unique to the specific columns
where your
data is located.

For example, if we tried using a divisor of 2 the remainder would be 0. But
this criteria includes columns that are not the specific columns of
interest. This criteria would include columns:

40,42,44,46,48,50,52

So, using a divisor of 6 sets the mod equal to 4 and this criteria is both
common and unique to only those columns of interest.

Biff

"T. Valko" <biffinpitt@xxxxxxxxxxx> wrote in message
news:eDWkkqoXHHA.3272@xxxxxxxxxxxxxxxxxxxxxxx
Why does the MOD function get set to "=4"?

We need to find a criteria that is common to the specific columns where
your data is located.

Column AN = column number 40
Column AT = column number 46
Column AZ = column number 52

This pattern of every 6th cell repeats to the end of your range.

What is common about those columns is that when you divide the column
number by 6 the remainder is 4:

=MOD(COLUMN(AN1),6) = 4
=MOD(COLUMN(AT1),6) = 4
=MOD(COLUMN(AZ1),6) = 4

The formula limits the calculation to only those columns where the mod of
the column number equals 4.

Biff

"SJT" <SJT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9D81E8AD-1C98-46B9-A0CB-0831539FB2A3@xxxxxxxxxxxxxxxx
That's great. You have been incredibly helpful. One last thing. Why
does
the MOD function get set to "=4"? Promise not to bother you w/ this
formula
again.

"T. Valko" wrote:

These expressions will return arrays of either TRUE or FALSE

(MOD(COLUMN(AN3:IV3),6)=4)
(AN$1:IV$1<=TODAY())

The double unary "--" coerces those logical values to numeric values:

--TRUE = 1
--FALSE = 0

See these for more info:

http://mcgimpsey.com/excel/formulae/doubleneg.html

http://xldynamic.com/source/xld.SUMPRODUCT.html

Biff

"SJT" <SJT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8E4089A3-D19C-4C7C-9258-AE98857C69E0@xxxxxxxxxxxxxxxx
Thank you so much for all of your assistance. BTW what does the "--"
before
and after the MOD function do? Thanks again. Really appreciate it.

"T. Valko" wrote:

I interpreted the post differently than you.

I read it to mean there is a single date for each month (the first of
each
month) spaced evenly every 6th cell starting from column AN and the
OP is
basically looking for a YTD sum.

Biff

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis)> wrote in message
news:1D4BE549-B5B4-40A0-81ED-96A589B9FAB0@xxxxxxxxxxxxxxxx
Damn, that's sweet - but take a look at it a little close, I'm
coming
up
short 2 months. I filled each of the columns from 3 to 15 with
increasing
values starting at 1, so for 12 months with date after 3rd, I'd
expect
to
get
a total of 12 for that row, but I'm only getting 12. I put your
formula
into
both columns AJ and AL with the same results: 10 instead of the
expected
12.
To test on out into the future I changed the formula to point to a
cell
with
a manually entered date instead of using TODAY() just to test this
kind
of
thing.

For my test date I used 1/4/2008 and just cannot get it to come up
with
anything but 10 for those.

But yours takes care of the whole situation much better than my
ugly
beastie
in general - specifically the values in rows corresponding to days
after
current day of the month.

"T. Valko" wrote:

Try this:

Row 1 = date headers

=SUMPRODUCT(--(MOD(COLUMN(AN3:IV3),6)=4),--(AN$1:IV$1<=TODAY()),AN3:IV3)

Adjust for the end of the range

Copy down as needed

Biff

"SJT" <SJT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BD9330C2-B01E-4216-942D-C2EB3627DCD8@xxxxxxxxxxxxxxxx
I think I'm in need of some type of index & match formula. I
have
column
headings that are dates and below them are a series of numbers.
I
would
like
a formula that adds the numbers below the column headings based
on
today's
date. For example, in column AN I have a column heading of
1/1/07
and
below
the column heading I have numerical data in rows 3 -15. In
column
AT I
have
the column heading 2/1/07 and in Column AZ, I have the column
heading
3/1/07.
This continues in the same pattern for each month of the year
and
below
each
column heading is data in rows 3 - 15. I would like to add the
cumulative
total of each row for each column depending on the date. Since
today
is
3/3,
for example I would like to add the total of Cells AT3 and AN3
in
one
cell
and then have a similar formula for each of the rows 4-15. On
4/1
this
formula would add to the previous totals the data in Column AZ
rows
3 -
15.
Would appreciate any help you could provide. Thank you in
advance
for
your
assistance.













.