Solved

EXCEL 2010 FORMULA/MACRO HELP

Posted on 2014-02-21
7
533 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
Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

739 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