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

 

[XOR LX]

It's under the Formulas tab.

It allows you to store ranges/formulas with set names, so that these may be referred to using just this name, rather than the entire formula/range reference.

Regards

 

[megamind007]

and please tell me if I want either "Payment" or "Credit" any one to appear in column C,then how would the formula change?

 

[XOR LX]

That would be a bit trickier, I believe.

Using the same Named Ranges as before, and assuming that your data begins in row 1 and goes no further than row 500 in each of the tabs (obviously adjust to suit your needs):

=SUMPRODUCT(((T(OFFSET(INDIRECT("'"&Sheet_Names&"'!A1"),ROW($1:$500)-1,,,))="1342-79002570")*((T(OFFSET(INDIRECT("'"&Sheet_Names&"'!C1"),ROW($1:$500)-1,,,))="Payment")+(T(OFFSET(INDIRECT("'"&Sheet_Names&"'!C1"),ROW($1:$500)-1,,,))="Credit"))*(T(OFFSET(INDIRECT("'"&Sheet_Names&"'!I1"),ROW($1:$500)-1,,,))="0-60 days")*N(OFFSET(INDIRECT("'"&Sheet_Names&"'!F1"),ROW($1:$500)-1,,,))))

Regards

 

[megamind007]

ghee..!
how do you come up with such formulas.
stand in your debt,

 

[XOR LX]

You're welcome.

 

[megamind007]

this formula is showing "#NAME?" error..!
any potential reasons?

<tbody>
</tbody><colgroup><col></colgroup>

 

[XOR LX]

Did you create the Named Ranges precisely as in my previous post?

Regards

 

[megamind007]

sorry I forgot that and now its working.
Thnx for your reply again.you are a great asset to this forum..!
best wishes,

 

[megamind007]

sorry to bother you again,but one more question.
If all other criteria remain same except that we have to count the no. of transactions matching the criteria instead of adding them,then how would the formula change?

 

[XOR LX]

No worries. If I understand you, just remove the final critierion:

*N(OFFSET(INDIRECT("'"&Sheet_Names&"'!F1"),ROW($1:$500)-1,,,))))

from the previous formula.

(Excel should autocorrect the number of brackets at the end of the new formula for you once you enter it.)

Regards