#value error SUMIFS formula across multiple sheets

[beerrunner]

Hi there,

I get a #value! error in my formula and have no clue about the reason. I work on an iMac with Excel 2007.

The file contains brewing reports and packaging reports. My goal is to have an overview of the duty I have to pay each month to our Customs Service. We are a craftbeer brewery in Holland. We pay duty accordingly to a system were beers are categorized. We have 4 categories, S (strong), 1,2 and 3 (lightest)

I'm not allowed to attache a simplified file to illustrate the problem, but I can mail it to anyone who's interested in solving this problem. I highlighted the cell that contains the formula on the first sheet. Be ware, there's some Dutch in there...


The formula looks lik this:

{=SUMPRODUCT(SUMIFS(INDIRECT("'"&$J$3:$J6&"'!$D$55:$ZZ$55");INDIRECT("'"&$J$3:$J6&"'!$D$51:$ZZ$51"); C3;INDIRECT("'"&$J$3:$J6&"'!$E$84:$ZZ$84");K3))}

$J$3:$J$6 is the table of sheetnames > jan-apr
$D$55:$ZZ$55 is the sum_range > amount of packaged liters beer
$D$51:$ZZ$51 is criteria_range1 > the duty category: S, 1, 2 of 3
C3 is de criteria1 > in this case the duty category S (not noted as "S" in the formula, but as reference to a cell containing this value.
$E$84:$ZZ$84 is criateria_range2 > this cel converts the packaging date (E43) to the name of the month > =TEXT(E43;"mmmm") )
K3 is the reference to the cell containing the month. In this case May

When I leave the second criteria, there's no problem Could it be that the formula to convert the date into the month has a TEXT output and therefor the formula can't read it?

Thank you very much!

 

[RoryA]

Welcome to the forum.

Your ranges are different sizes - columns D:ZZ for the first two ranges and E:ZZ for the third.

 

[steve the fish]

You cant have different length ranges. So you cant use D:ZZ then use E:ZZ.

 

[beerrunner]

okay, thnx!

I'll adjust the sheets and see what happens

 

[beerrunner]

Works perfectly!

Many thanks guys!