?
Solved

Why does my array formula not work here

Posted on 2014-09-21
4
Medium Priority
?
156 Views
Last Modified: 2014-09-21
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
Comment
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
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 50

Expert Comment

by:barry houdini
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

by:Jorgen
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

by:
barry houdini earned 2000 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

by:Jorgen
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question