sumproduct - validating codes & dates

C

Chuck

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")),"000")&"'!
$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")),"000")&"'!
$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
 
H

Herbert Seidenberg

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 Insert>Name>Define
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.
 
C

Chuck

hi Herbert,

i have decided to put the excel doc here
http://www.aais.com.au/downloads/Project_Purchasing_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
 
H

Herbert Seidenberg

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top