Sumproduct - several sheets ... indirect?

[JB]

Hello,

I'm trying to sum a number of values depending on if three different
criterias are met. Formula at the moment (one sheet):

=SUMPRODUCT((YEAR('09'!$N$7:$N$500)=YEAR($C$2))*(MONTH('09'!$N$7:$N$500)=MONTH($C$2))*('09'!$E$7:$E$500=A28)*'09'!$P$7:$P$500)

Now let's say that I'd like to sum values not just from year '09' but from
00-09, how do I do that?

I've looked around at other similiar questions but I still can't solve the
problem...

Thanks in advance!

//JB

 

[NBVC]

Just a create a new Sumproduct just like the last and add to it..

e.g


Code:

 

[JB]

Oh sorry, forgot to mention that would prefer not to do it that way.

Thanks though trying to help out.

/JB

"NBVC" skrev:

 

[JB]

hmm I probably need to clarify:
I have one sheet for each year between 91-09 and would like something like
'91:09' instead of copying the formula over and over.

"NBVC" skrev:

 

[NBVC]

If you download and install a free addin called Morefunc.xll from here:

'Morefunc - Free software downloads and reviews - CNET Download.com'
(http://download.cnet.com/Morefunc/3000-2077_4-10423159.html)

Then you can apply the THREED() function to span the sheets...


Code:

 

[Don Guillett]

Hard to visualize exactly what you want so.

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Jacob Skaria]

I am not sure whether you still need the month to be checked. If not remove
the mnth condition...Try and feedback..

=SUMPRODUCT(--(YEAR('09'!$N$7:$N$500)>=2000),--(YEAR('09'!$N$7:$N$500)<=2009),--(MONTH('09'!$N$7:$N$500)=MONTH($C$2)),--('09'!$E$7:$E$500=A28),
'09'!$P$7:$P$500)

If this post helps click Yes

 

[JB]

I'll try to explain the issue a bit better:

I have a one sheet for each year from 91 to 09, plus a cover sheet.

Sheet 91 to 09 all look the same except for differnet values. In column "E"
I have account number, in column "N" I have dates (eg. 2009-01-01) and in
column "P" I have numerical values.

What I want to do is to summarize values from column P (from all sheets) if
the corresponding account number, month and year are equal to the "users
choice".
The user types the desired date in cell C2 on the cover sheet and the
desired account number in cell A28, see formula:

=SUMPRODUCT((YEAR('09'!$N$7:$N$500)=YEAR($C$2))*
(MONTH('09'!$N$7:$N$500)=MONTH($C$2))*
('09'!$E$7:$E$500=A28)*'09'!$P$7:$P$500)

Looking at my current formula (which works for ONE sheet, in this case '09')
it might be easier to understand what I'm trying to do.

What I could do is to do like this:

=SUMPRODUCT((YEAR('09'!$N$7:$N$500)=YEAR($C$2))*
(MONTH('09'!$N$7:$N$500)=MONTH($C$2))*
('09'!$E$7:$E$500=A28)*'09'!$P$7:$P$500)
+
SUMPRODUCT((YEAR('08'!$N$7:$N$500)=YEAR($C$2))*
(MONTH('08'!$N$7:$N$500)=MONTH($C$2))*
('08'!$E$7:$E$500=A28)*'08'!$P$7:$P$500)
+
=SUMPRODUCT((YEAR('07'!$N$7:$N$500)=YEAR($C$2))*
(MONTH('07'!$N$7:$N$500)=MONTH($C$2))*
('07'!$E$7:$E$500=A28)*'07'!$P$7:$P$500)
and so on...

Did it get any clearer?

Thanks!

 

[Don Guillett]

One way may be to put the sumproduct formula in the same place (a2) on each
sheet referring to the master sheet a28 and c2 and then use this formula in
the master sheet.
=sum(firstsheet:lastsheet!a2)

 

[NBVC]

I'll try to explain the issue a bit better:

I have a one sheet for each year from 91 to 09, plus a cover sheet.

Sheet 91 to 09 all look the same except for differnet values. In column
"E"
I have account number, in column "N" I have dates (eg. 2009-01-01) and
in
column "P" I have numerical values.

What I want to do is to summarize values from column P (from all
sheets) if
the corresponding account number, month and year are equal to the
"users
choice".
The user types the desired date in cell C2 on the cover sheet and the
desired account number in cell A28, see formula:

=SUMPRODUCT((YEAR('09'!$N$7:$N$500)=YEAR($C$2))*
(MONTH('09'!$N$7:$N$500)=MONTH($C$2))*
('09'!$E$7:$E$500=A28)*'09'!$P$7:$P$500)

Looking at my current formula (which works for ONE sheet, in this case
'09')
it might be easier to understand what I'm trying to do.

What I could do is to do like this:

=SUMPRODUCT((YEAR('09'!$N$7:$N$500)=YEAR($C$2))*
(MONTH('09'!$N$7:$N$500)=MONTH($C$2))*
('09'!$E$7:$E$500=A28)*'09'!$P$7:$P$500)
+
SUMPRODUCT((YEAR('08'!$N$7:$N$500)=YEAR($C$2))*
(MONTH('08'!$N$7:$N$500)=MONTH($C$2))*
('08'!$E$7:$E$500=A28)*'08'!$P$7:$P$500)
+
=SUMPRODUCT((YEAR('07'!$N$7:$N$500)=YEAR($C$2))*
(MONTH('07'!$N$7:$N$500)=MONTH($C$2))*
('07'!$E$7:$E$500=A28)*'07'!$P$7:$P$500)
and so on...

Did it get any clearer?

Thanks!

Have you tried the THREED() suggestion of my previous post?

 

[JB]

No, I didn't try it because I'm not allowed to install it. In will have a
look at it later though!

Don's suggestion worked for me so problem solved anyway, thank you all for
helping out!

Cheers,
JB

"NBVC" skrev: