Why does my array formula not work here

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
LVL 4
JorgenConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
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
JorgenConsultantAuthor Commented:
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
barry houdiniCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JorgenConsultantAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.