Thanks, Domenic

I will check out your formula. Since I was out of my wits, I redessigned my
sheets using 6 weeks instead of 5 and the formula I had come up with, so that
all the dates would fall on the same month. But I am going to check your idea
to see what happens and to learn from it.

Again, thanks

"Domenic" wrote:

Bernie has provide some excellent advice. In the meantime, to sum
Hoja3, Hoja4, and Hoja5 based on the criteria you've laid out, try....

=SUMPRODUCT(--(N(OFFSET(INDIRECT("'Hoja"&{3,4,5}&"'!B2:B13"),ROW (INDIRECT
("2:13"))-2,0,1))=A19),--(N(OFFSET(INDIRECT("'Hoja"&{3,4,5}&"'!B2:B13"),
ROW(INDIRECT("2:13"))-2,0,1))<=A20),N(OFFSET(INDIRECT("'Hoja"&{3,4,5}&"' !
C2:C13"),ROW(INDIRECT("2:13"))-2,0,1))+N(OFFSET(INDIRECT("'Hoja"&{3,4,5}&
"'!D2:D13"),ROW(INDIRECT("2:13"))-2,0,1)))

Hope this helps!

http://www.xl-central.com

In article ,
Bert T wrote:

Hi!

I have a book in Excel 2003 that serves as simple timetable and payroll at
the same time, but assignments are scheduled weekly from Mon €€œ Sun, which is
also the way the employees are paid. That part works perfectly until I have
to do the tax analysis, since its breakdown goes by month and year.

I have 12 sheets, each for a month in a year and each sheet is made out of 5
weeks. I created them like this because the last days of a month and the
beginning of the next can fall on the same week - either on the 5th week of
that month or on the 1st of the next. The five weeks also gives the users
enough flexibility to organize their weeks as they please.

The amount paid each month is figured out from columns C2:C13 (Payments) and
D2:D13 (Extras) by their dates in column B2:B13 (this are just a few of the
cells in each range, but their similar) with the following formula:
=SUMAPRODUCTO(--(B$2:B$13=A19);--(B$2:B$13<=A20);(C$2:C$13+D$2:D$13))

The CATCH is that the prior and/or following sheet might also hold dates
from this month. So assuming we're looking at sheet4, the other two are
sheet3 and sheet5. My Excel is in Spanish so SUMAPRODUCTO is SUMPRODUCT and
Sheet is Hoja.

SUMAPRODUCTO(--(Hoja3!B2:B13=A19);--(Hoja3!B2:B13<=A20);(Hoja3!C$2:C$13+Hoja3
!D$2:D$13))

SUMAPRODUCTO(--(Hoja5!B$2:B1$3=A19);--(Hoja5!B$2:B$13<=A$20);(Hoja5!C$2:C$13+
Hoja5!D$2:D$13))

A19 and A20 are equivalent to the first and last date of the month and I
used them as criteria for the date search. Hoja3 is the prior month and Hoja5
is the following

I don€„¢t want to have 48 formulas to contend with, but only 12.
Is there any way to nest 3 3D SUMPRODUCT formulas in one that sums their
product? B21 would hold this formula and its product. Something like
SP(SP()+SP()+SP()) or maybe SUM(SP();SP();SP()) Any ideas?

I remember seeing this formula somewhere and that€„¢s why I ask:
=SUMPRODUCT((B1:B10="Europe")+(C1:C10="Y"))-SUMPRODUCT(--(B1:B10="Europe"),--(
C1:C10="Y"))

I would really appreciate any help.
Thanks