Solved

# Why does my array formula not work here

Posted on 2014-09-21
150 Views
I have taken over an Excel training from a coleague, where I have to show array calculations.

This is all fine, and the exercise Works fine apart from the last part.

I have a report with customer sales (Omsætning) and calculate the customer rebate (kunderabat) as an array formula. this Works fine.

Now I have to calculate the total sum of customer rebate for 4 specific customers. They are listed in Range K8:K11, and I want the calculation done as a new arrayformula in Range L8:L11.

I tried marking all 4 rows and inserting the formula =sum(if(Kunde=K8,Kunderabat,0) finishing with CTRL + SHIFT + ENTER. that gives me the result for the first customer in all 4 cells.

I tried to  change the formula to =sum(if(Kunde=K8:K11,Kunderabat,0) but then I got no result at all

It all Works fine if I do this cell by cell and changing K8 to K9 in the Next cell etc.

What do I do wrong - and can it be solved in other ways?

best regards

Jørgen
Kunderabat.xlsx
0
Question by:Jorgen
[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
• 2
• 2

LVL 50

Expert Comment

ID: 40335329
You don't have to change the formula manually, you can copy the formula down and K8 will change automatically on each row.

Try this:

Delete all current formulas in L8:L11

Insert this formula in L8

=SUMIF(Kunde,K8,Kunderabat)

That doesn't need "array entry"

Now copy down by selecting that cell, place cursor on bottom right of cell until you see a black "+" - that's the fill-handle - hold down left mouse button and drag down to L11. As I say K8 will change as you drag down

regards, barry
0

LVL 4

Author Comment

ID: 40335380
Hi Barry

that is indead a solution, that Works. The problem though is that the lesson is related to array calculations and the material is send to the participants.

So I guess I need to come up with an array solution :-(

regards
Jørgen
0

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 40335438
OK, if you want a single array formula entered in a range try this

=SUMIF(Kunde,K8:K11,Kunderabat)

Place in L8, then select the whole range L8:L11 and use CTRL+SHIFT+ENTER to confirm

regards, barry
0

LVL 4

Author Closing Comment

ID: 40335465
I know that we should look for the easy solution, but in this case you gave me something that was needed more than the best. Thanks.

And if I will do the training Again - I will rewrite this part.

Jørgen
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…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month7 days, 2 hours left to enroll