Solved

EXCEL 2010 FORMULA/MACRO HELP

Posted on 2014-02-21
7
522 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
  • 4
  • 2
7 Comments
 
LVL 11

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 11

Expert Comment

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

Cancel my pathetic attempt, pls Gail.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 11

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 11

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

757 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

22 Experts available now in Live!

Get 1:1 Help Now