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))*(M ONTH('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

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

e.g


Code:
--------------------
=SUMPRODUCT((YEAR('09'!$N$7:$N$500)=YEAR($C$2))*(M ONTH('09'!$N$7:$N$500)=MONTH($C$2))*('09'!$E$7:$E$ 500=A28)*'09'!$P$7:$P$500)+=SUMPRODUCT((YEAR('00-09'!$N$7:$N$500)=YEAR($C$2))*(M ONTH('00-09'!$N$7:$N$500)=MONTH($C$2))*('00-09'!$E$7:$E$ 500=A28)*'00-09'!$P$7:$P$500)
--------------------


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

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

Thanks though trying to help out.

/JB

"NBVC" skrev:


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

e.g


Code:
--------------------
=SUMPRODUCT((YEAR('09'!$N$7:$N$500)=YEAR($C$2))*(M ONTH('09'!$N$7:$N$500)=MONTH($C$2))*('09'!$E$7:$E$ 500=A28)*'09'!$P$7:$P$500)+=SUMPRODUCT((YEAR('00-09'!$N$7:$N$500)=YEAR($C$2))*(M ONTH('00-09'!$N$7:$N$500)=MONTH($C$2))*('00-09'!$E$7:$E$ 500=A28)*'00-09'!$P$7:$P$500)
--------------------


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

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:


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

e.g


Code:
--------------------
=SUMPRODUCT((YEAR('09'!$N$7:$N$500)=YEAR($C$2))*(M ONTH('09'!$N$7:$N$500)=MONTH($C$2))*('09'!$E$7:$E$ 500=A28)*'09'!$P$7:$P$500)+=SUMPRODUCT((YEAR('00-09'!$N$7:$N$500)=YEAR($C$2))*(M ONTH('00-09'!$N$7:$N$500)=MONTH($C$2))*('00-09'!$E$7:$E$ 500=A28)*'00-09'!$P$7:$P$500)
--------------------


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

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

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

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


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


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

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.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JB" wrote in message
...
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:


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

e.g


Code:
--------------------

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


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

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
---------------
Jacob Skaria


"JB" wrote:

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))*(M ONTH('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

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!

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)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JB" wrote in message
...
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!

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

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