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