Solved

EXCEL 2010 FORMULA/MACRO HELP

Posted on 2014-02-21
7
525 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 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
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 tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

803 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