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:


JB;408060 Wrote:
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?


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113712