Solved

EXCEL 2010 FORMULA/MACRO HELP

Posted on 2014-02-21
7
534 Views
Last Modified: 2014-02-24
Hi, would really appreciate if you can help with a rather complicated formula (at least it is for me!):
In the attached spreadsheet - Excel 2010:

Columns A:H contain the full list of hotels and booking details

Columns J and K contain a unique list of the hotels and their city (using Remove Duplicates)

Note:   because there may be many other rows later I have extended the full A:H range to 5000 rows in the specification below

Note:  I can write this as a pure FORMULA in cell L2 OR as a MACRO - (if it is a formula it will eventually be incorporated into a Macro)

I need:

Cell L2:  IF in the range A2:A5000=J2 AND in the range B2:B5000=K2  THEN   SUM the relevant cells in Column C

Using relative references in the formula above I will then be able to copy the formula down into the rest of the L column
and
I will also be able to adapt the formula to calculate the M, N, O and P columns

Many thanks

Gail

Cell L2:  IF
Query1.xlsx
0
Comment
Question by:GAILBAKER
[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
  • 4
  • 2
7 Comments
 
LVL 12

Expert Comment

by:tel2
ID: 39878562
Hi Gail,

What do you mean by "SUM the relevant cells in Column C", exactly?

I assume you'll put something like this in L2, then copy it down:
    =IF(AND(A2=J2,B2=K2), what-to-do-if-true, what-to-do-if-false)
But the "what-to-do-if..." parts depend on your answer to my question above.
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39878589
You can use the Sumifs formula.

Check the attached file.
Query1.xlsx
0
 
LVL 12

Expert Comment

by:tel2
ID: 39878600
Nice work, Harry...IMHO.

Cancel my pathetic attempt, pls Gail.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:GAILBAKER
ID: 39878663
Tel2 - You are ALL brilliant at Experts Exchange and we mere mortals are lucky to have you!!

Gail
0
 
LVL 12

Expert Comment

by:tel2
ID: 39878941
I'm deeply honoured, Gail.  Or at least shallowly.

Are you one of those paying members?  Looks as if you asked 5 questions in 2013.  What does that cost you?  Is it worth it?  I've often wondered.
0
 

Author Comment

by:GAILBAKER
ID: 39878950
Yes - I have been a paying member for several years (i.e. for as long as I have been creating Excel and Access models for my clients).  It costs me $99.50 and is worth every cent.

Where in the world are you (I'm in summery Sydney)

G
0
 
LVL 12

Expert Comment

by:tel2
ID: 39881276
Hi G (now that we're on 1st initial terms),

I'm across the ditch in windy Wellington, but please don't hold that against me.

My Scottish ancestry won't permit me to pay a cent for such answers.  Instead I have to answer some questions (at least 3000 points worth) every couple of months, to pay my way.  That's 1.5 500 point questions, if I get an A grade.  Sometimes that takes me hours.

T
0

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

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…
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

729 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