Can SUMPRODUCT work on changing arrays?

  • Thread starter Thread starter Arlen
  • Start date Start date
A

Arlen

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
 
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)
 
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,

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,

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
 
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)
 
Back
Top