Function argument not returning a value

[Linda]

Hi All,

Quick question: I'm using the SUM fx in order to create a formula that would
return a value based on information in a different worksheet within the same
workbook. When I key in the formula and hit ENTER, I get a 0 in the cell of
interest. However, when I click on the fx button at the top, it shows my
formula and my formula result as 20.75. Anyone know why the cell won't show
20.75 if the fx argument is calculating correct?
Thanks a million,
Linda

 

[FSt1]

hi
post your formula please.

Regards
FSt1

 

[Linda]

=SUM((Master!$A$6:$A$504="January")*(Master!$C$6:$C$504="LBK")*Master!$H$6:$H$504)

 

[Max]

=SUM((Master!$A$6:$A$504="January")*(Master!$C$6:$C$504="LBK")*Master!$H$6:$H$504)

It's an array formula. If you array-enter it in the cell, ie press
CTRL+SHIFT+ENTER instead of just pressing ENTER, it'll return the correct
result. Just click inside the formula bar again, then press CTRL+SHIFT+ENTER.
If you do it correctly, Excel will wrap curly braces around the formula: { }.
You should see these braces in the formula bar as a visual check that the
formula is correctly array entered.

---

 

[RagDyer]

Your formula is an *array* formula.
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

However, you could convert it to the Sumproduct() function, and then use a
simple <Enter>:

=Sumproduct((Master!$A$6:$A$504="January")*(Master!$C$6:$C$504="LBK")*Master!$H$6:$H$504)

 

[T. Valko]

Or, you could use this non-array version.

Normally entered:

=SUMPRODUCT(--(Master!$A$6:$A$504="January"),--(Master!$C$6:$C$504="LBK"),Master!$H$6:$H$504)

Better to use cells to hold the criteria:

A1 = January
B1 = LBK

=SUMPRODUCT(--(Master!$A$6:$A$504=A1),--(Master!$C$6:$C$504=B1),Master!$H$6:$H$504)

 

[Linda]

SWEET, it worked!!! Thank you sooo much for that , I truly appreciate it!

Linda

 

[Linda]

Thanks a million!!!

Linda

Or, you could use this non-array version.

Normally entered:

=SUMPRODUCT(--(Master!$A$6:$A$504="January"),--(Master!$C$6:$C$504="LBK"),Master!$H$6:$H$504)

Better to use cells to hold the criteria:

A1 = January
B1 = LBK

=SUMPRODUCT(--(Master!$A$6:$A$504=A1),--(Master!$C$6:$C$504=B1),Master!$H$6:$H$504)

 

[Linda]

Thanks so much!

Linda

Your formula is an *array* formula.
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

However, you could convert it to the Sumproduct() function, and then use a
simple <Enter>:

=Sumproduct((Master!$A$6:$A$504="January")*(Master!$C$6:$C$504="LBK")*Master!$H$6:$H$504)

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks a million!!!

Linda

 

[Max]

welcome, Linda.

 

[RagDyer]

Appreciate the feed-back.