On Mar 27, 7:52 pm, Farhad wrote:
Hi,

Sorry the correct formula is:

=SUMPRODUCT(--(SHEET2!A1:A100=C2),--(SHEET2!B1:B100<=E2),--(SHEET2!AT1:AT1*00))

Thanks,
--

Farhad Hodjat



"Farhad" wrote:
Hi,

try this:

in the cell AT3 Sheet3 enter this formula:

=SUMPRODUCT(--(SHEET2!A1:A100=C2),--(SHEET2!B1:B100=E2),--(SHEET2!AT1:AT100*)).

Thanks,

PS: you can change the ranges "A1:A100", "B1:B100" and "AT1:AT100"
--
Farhad Hodjat

"j razz" wrote:

Here is my issue. I have a spreadsheet made up of three sheets.

BACKGROUND:
Sheet one is a skeleton sheet that information is dumped into on a
daily basis.

Sheet two tabulates the data into totals and such based on the
criteria set up for this spreadsheet in weekly increments with a total
of 56 weeks.

Sheet three takes the totals from sheet two and lists them in a
concise manner.

ISSUE:
I have several variables to contend with to finish sheet 3:
1. It is date dependant. (It must fall within this range of dates-
eg 3-18-2007 to 3-24-2007)
2. It is criteria specific. (It must be under a certain category- eg
all items for the week specified above that fall under CKT)
3. Sheet 2 has all the start dates in column A and all the end dates
in column B and all the criteria specific information in column AT
4. The user needs to be able to specify the date range they want to
view the totals for. (Eg Cell C2 would contain the start date-
3-18-2007 and cell E2 would contain the end date 3-24-2007)
5. Cell B4 needs to populates with the correct total for that date
range specified.

GOAL:
I would like to have it to where if the user typed in the date range
in cells C2 and E2 on sheet 3 the date range would be found on sheet 2
and then the accurate number would be pulled from the corresponding AT
cell on sheet 2 and plugged into cell B4 on sheet 3. (In this
instance the date 3-18-2007 is in cell A3, the date 3-24-2007 is in
cell B3 and the corresponding total is in cell AT3- all this is on
sheet 2)

I would very much appreciate the help on this. Thanks

j razz- Hide quoted text -

- Show quoted text -

Thank you, it works great for one variable. How do I combine or nest
these formulas together?

=SUMPRODUCT(--(Tabulation!$A$3:$A$54=$C$2),--(Tabulation!$B$3:$B$54<=
$E$2),--(Tabulation!AU$3:AU$54)) with =SUMPRODUCT(--(Tabulation!$A$3:$A
$54=$C$2),--(Tabulation!$B$3:$B$54<=$E$2),--(Tabulation!AV$3:AV$54))
with =SUMPRODUCT(--(Tabulation!$A$3:$A$54=$C$2),--(Tabulation!$B$3:$B
$54<=$E$2),--(Tabulation!AW$3:AW$54)) with =SUMPRODUCT(--(Tabulation!$A
$3:$A$54=$C$2),--(Tabulation!$B$3:$B$54<=$E$2),--(Tabulation!AX$3:AX
$54)) with =SUMPRODUCT(--(Tabulation!$A$3:$A$54=$C$2),--(Tabulation!$B
$3:$B$54<=$E$2),--(Tabulation!AY$3:AY$54))