Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel - Why isn't this formula working?

Posted on 2011-09-20
Medium Priority
170 Views
Hi Excel Experts,

How come this formula works in one of my workbooks but not another?

Combined-Inventory-09-30-10-New.xlsx Combined-Inventory--August-2011-.xlsx

I am trying to do a fairly simple formula:  =SUM((\$C\$4:\$C\$1272=\$C\$1275)*(\$Q\$4:\$Q\$1272))

Whenever I put it in, i get #Value.

Why?
0
Question by:activematx
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 36570064
That's an "array formula" - you need to enter it and then confirm with CTRL+SHIFT+ENTER

To do that press F2 key to select formula then hold down CTRL and SHIFT keys and press ENTER so that curly braces like { and } appear around the formula in the formula bar.

It's simpler, though, in this case to use a non-array SUMIF formula to get the same result, i.e.

=SUMIF(\$C\$4:\$C\$1272,\$C\$1275,\$Q\$4:\$Q\$1272)

regards, barry
0

LVL 9

Author Comment

ID: 36570190
Thanks so much Barry!  You saved me so much time!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month5 days, 13 hours left to enroll