Can SUMPRODUCT work on changing arrays?
 

I am making a new workbook to collect sums and counts from several other
archived workbooks, based on the date.

My archives have sheet of data like this:

A | B | C

1 Date Trailer Gallons
2 Wed 02/29/07
3 66 1599
4 148 9000
5 Thu 03/01/07
6 66 8008
7 Fri 03/02/07
8 148 21398

In my new book, I am using SUMPRODUCT on a static range "B4:B100." What I
need is for the Feb sheet to only SUMPRODUCT the data under 02/29/07 and the
Mar sheet to grab its own numbers off the same sheet.

So far, I've only managed to accomplish this by adding a formula to the
archived sheet that adds zeros to the array if the months don' t match. But
how can I accomplish the same thing with only a formula on the new sheet.

I appreciate any help.

Arlen

Can SUMPRODUCT work on changing arrays?
 

Add a test of

--(MONTH(A4:A100)=2)

for Feb. =3 for March to the formula

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Arlen" wrote in message
...
I am making a new workbook to collect sums and counts from several other
archived workbooks, based on the date.

My archives have sheet of data like this:

A | B | C

1 Date Trailer Gallons
2 Wed 02/29/07
3 66 1599
4 148 9000
5 Thu 03/01/07
6 66 8008
7 Fri 03/02/07
8 148 21398

In my new book, I am using SUMPRODUCT on a static range "B4:B100." What I
need is for the Feb sheet to only SUMPRODUCT the data under 02/29/07 and
the
Mar sheet to grab its own numbers off the same sheet.

So far, I've only managed to accomplish this by adding a formula to the
archived sheet that adds zeros to the array if the months don' t match.
But
how can I accomplish the same thing with only a formula on the new sheet.

I appreciate any help.

Arlen

Can SUMPRODUCT work on changing arrays?
 

Bob,

Would that be an IF(--MONTH(A4:A100)=2, SUMPRODUCT if true?

And what IF False? It can't do nothing. This is where I'm confused.

Thank you thus far.

Arlen

"Bob Phillips" wrote:

Add a test of

--(MONTH(A4:A100)=2)

for Feb. =3 for March to the formula

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Arlen" wrote in message
...
I am making a new workbook to collect sums and counts from several other
archived workbooks, based on the date.

My archives have sheet of data like this:

A | B | C

1 Date Trailer Gallons
2 Wed 02/29/07
3 66 1599
4 148 9000
5 Thu 03/01/07
6 66 8008
7 Fri 03/02/07
8 148 21398

In my new book, I am using SUMPRODUCT on a static range "B4:B100." What I
need is for the Feb sheet to only SUMPRODUCT the data under 02/29/07 and
the
Mar sheet to grab its own numbers off the same sheet.

So far, I've only managed to accomplish this by adding a formula to the
archived sheet that adds zeros to the array if the months don' t match.
But
how can I accomplish the same thing with only a formula on the new sheet.

I appreciate any help.

Arlen

Can SUMPRODUCT work on changing arrays?
 

Bob,

Also, don't I have to make each instance of the trailer number in Col B look
left and up to the next date cell above it to determine what month it is in?
Is that accomplished with OFFSET inside the SUMPRODUCT?

Forgive me. I've seen other posts that talk about these functions, but I'm
apparently a tough study in these matters.

Arlen

"Bob Phillips" wrote:

Add a test of

--(MONTH(A4:A100)=2)

for Feb. =3 for March to the formula

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Arlen" wrote in message
...
I am making a new workbook to collect sums and counts from several other
archived workbooks, based on the date.

My archives have sheet of data like this:

A | B | C

1 Date Trailer Gallons
2 Wed 02/29/07
3 66 1599
4 148 9000
5 Thu 03/01/07
6 66 8008
7 Fri 03/02/07
8 148 21398

In my new book, I am using SUMPRODUCT on a static range "B4:B100." What I
need is for the Feb sheet to only SUMPRODUCT the data under 02/29/07 and
the
Mar sheet to grab its own numbers off the same sheet.

So far, I've only managed to accomplish this by adding a formula to the
archived sheet that adds zeros to the array if the months don' t match.
But
how can I accomplish the same thing with only a formula on the new sheet.

I appreciate any help.

Arlen

Can SUMPRODUCT work on changing arrays?
 

Bob,

Hey, I figured out how to use the Month statement. If you're still around,
could you help me with the OFFSET? I'll keep trying it till I hear from you.

Thanks,

Arlen

"Bob Phillips" wrote:

Add a test of

--(MONTH(A4:A100)=2)

for Feb. =3 for March to the formula

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Arlen" wrote in message
...
I am making a new workbook to collect sums and counts from several other
archived workbooks, based on the date.

My archives have sheet of data like this:

A | B | C

1 Date Trailer Gallons
2 Wed 02/29/07
3 66 1599
4 148 9000
5 Thu 03/01/07
6 66 8008
7 Fri 03/02/07
8 148 21398

In my new book, I am using SUMPRODUCT on a static range "B4:B100." What I
need is for the Feb sheet to only SUMPRODUCT the data under 02/29/07 and
the
Mar sheet to grab its own numbers off the same sheet.

So far, I've only managed to accomplish this by adding a formula to the
archived sheet that adds zeros to the array if the months don' t match.
But
how can I accomplish the same thing with only a formula on the new sheet.

I appreciate any help.

Arlen

Can SUMPRODUCT work on changing arrays?
 

Arlen,

I am not sure what you are doing/trying to do.

Can you give an example of the data, what results you want, and what you
have tried. Be careful with the layout, newsgroups can mess it up.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Arlen" wrote in message
...
Bob,

Hey, I figured out how to use the Month statement. If you're still
around,
could you help me with the OFFSET? I'll keep trying it till I hear from
you.

Thanks,

Arlen

"Bob Phillips" wrote:

Add a test of

--(MONTH(A4:A100)=2)

for Feb. =3 for March to the formula

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Arlen" wrote in message
...
I am making a new workbook to collect sums and counts from several other
archived workbooks, based on the date.

My archives have sheet of data like this:

A | B | C

1 Date Trailer Gallons
2 Wed 02/29/07
3 66 1599
4 148 9000
5 Thu 03/01/07
6 66 8008
7 Fri 03/02/07
8 148 21398

In my new book, I am using SUMPRODUCT on a static range "B4:B100."
What I
need is for the Feb sheet to only SUMPRODUCT the data under 02/29/07
and
the
Mar sheet to grab its own numbers off the same sheet.

So far, I've only managed to accomplish this by adding a formula to the
archived sheet that adds zeros to the array if the months don' t
match.
But
how can I accomplish the same thing with only a formula on the new
sheet.

I appreciate any help.

Arlen