#Value

[megamind007]

Hey folks,

=SUMIFS('June & Before:December'!F:F,'June & Before:December'!A:A,"1342-79002570",'June & Before:December'!C:C,"Payment",'June & Before:December'!I:I,"0-60 days")

whe the abovementioned formula is displaying value error.

[megamind007]

P.S: 'June & Before' and 'December' are sheet names and there are several sheets in between them.

[jsotola]

click on the cell with the formula

click the fx button at left end of formula bar

the popup dialog may point you to the source of the error


maybe the sum range is meant to be only one cell, not the whole column

[megamind007]

it doesnt point to any error..

[jsotola]

so the "function arguments" dialog box is showing expected values on right side?

nothing in red?

[megamind007]

not for "sum_range" it shows blank

[jsotola]

that is the destination for the result

maybe your data is not numeric

[megamind007]

nop its numeric.

[XOR LX]

Hi,

You cannot perform the 3D sum as you currently have it laid out. You would require (I am guessing at your other intermediary sheet names here):

=SUMPRODUCT(SUMIFS(INDIRECT("'"&{"June & Before","July","August","September","October","November","December"}&"'!F:F"),INDIRECT("'"&{"June & Before","July","August","September","October","November","December"}&"'!A:A"),"1342-79002570",INDIRECT("'"&{"June & Before","July","August","September","October","November","December"}&"'!C:C"),"Payment",INDIRECT("'"&{"June & Before","July","August","September","October","November","December"}&"'!I:I"),"0-60 days"))

Even better, since this is quite ungainly and inflexible, would be to go to Name Manager and create a new name, Sheet_Names say, with this in the Refers to: box:

={"June & Before","July","August","September","October","November","December"}

or whatever the actual sheets are in queston.

The formula then becomes:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheet_Names&"'!F:F"),INDIRECT("'"&Sheet_Names&"'!A:A"),"1342-79002570",INDIRECT("'"&Sheet_Names&"'!C:C"),"Payment",INDIRECT("'"&Sheet_Names&"'!I:I"),"0-60 days"))

That way, if ever you need to add/change/delete a sheet from the summation, you can simply make the necessary amendments in the defined name, and the formula will update accordingly.

Regards

[megamind007]

first off thanks alot as it worked for me,but if you could tell how "names manager" works.
much obliged,
thanks in anticipation,