Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

EXCEL 2010 FORMULA/MACRO HELP

Posted on 2014-02-21
7
Medium Priority
?
537 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 2000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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…
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 …
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.

721 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