Solved

Why does my array formula not work here

Posted on 2014-09-21
4
145 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
  • 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 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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now