Solved

Why does my array formula not work here

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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