hi guys,

i need sumproduct to validate two things for it to produce a sum


1. needs to look at all tabs (001-013) and see if the month actually
has a value and if it matches the value in D8
2. needs to again look at all tabs and look at range $C$24:$C$37 has a
value and match it to the range in B55:B78
3. if date & code match, produce the sum


so


TABS 01-013
Month(H:6) = Date
C24:C37 = Code match
J24:J37 = Total Value of that code

Where the formual will sit (range C55:N78)
B55:B78 = Code the tabs will validate against
C8:N8 = Month(Date) of which the tabs will validate against

i have tried the following
=SUMPRODUCT(--
(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"00 0")&"'!
$C$24:$C$37"),$B55,INDIRECT("'"&TEXT(ROW(INDIRECT( "1:13")),"000")&"'!
$J
$24:$J$37"))))
the above works and provides me the value of the Codes, but no
validation against month

so i tried this
=SUMPRODUCT(--
(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"00 0")&"'!
$C$24:$C$37"),$B55,INDIRECT("'"&TEXT(ROW(INDIRECT( "1:13")),"000")&"'!
$J
$24:$J$37"))),(--
MONTH(INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000 ")&"'!$H$6"))=$D
$8))
but all i get is #VALUE

any help on this would be great


Cheers

There are only certain functions that work with 3D references.
SUMIF and SUMPRODUCT are not among them.
Search Help 3D for the list of allowed functions.
Your formulas can be simplified if you use defined names.
If you define Code ='001:013'!C24:C37 at InsertNameDefine
then INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'! $C$24:$C
$37")
reduces to Code
A small simplified example with inputs and desired outputs would help.

hi Herbert,

i have decided to put the excel doc here
http://www.aais.com.au/downloads/Pro...g_Template.zip

figured it was easier to do to help in my explination

the tab of which i am trying to fix is the calederised tab. you can
see from tehre taht there is a whole lot of # VALUE errors. the rest
that do not have it is because i am trying to amend formula's ect. but
that whole table is where the formula would reside

if you can, can you please see the attached?

cheers
chucks

A clever way to do a 3D AND lookup.
Due to the limitations of this unconventional use, you have to add
some cells to your sheets.
Add a cell named POMonth
=MONTH(PO_Date)
to each 001:013 sheet
and create a 14 cell vertical vector on one sheet, named Multi.
={1;0;0;0;0;0;0;0;0;0;0;0;0;0}
The formula now is
SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT("1 :13")),"000")&"'!
PO_GLCode"),
$B55,INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000" )&"'!UnitTotal"))*
SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000 ")&"'!POMonth"),C
$8,Multi))
If you want me to send the file to a downloadable site, please give me
instructions.