Some help needed with IF(AND... formula please

**jason7579** · 08-07-2009, 10:31 AM

I have a table with 5 columns and approx. 85-90 rows.

Column A has the Branch name in it e.g. Beavers or Bedfont (11 Branches in total)
Column B has User Type - Adult, Child, Guest (Adult), Guest (Child), Catalogue
Column C has Session Type - Booking, Drop-In
Column D has Total Session Time (mins) - which gives a number in minutes of the total session time used
Column E is not needed

I currently get a calculator and add up e.g all of the adult Bookings for Beavers and enter them onto a Report Sheet, then all of the Adult Drop-Ins for Beavers etc...

I want an Excel Spreadsheet that will give me a total number for each so I can do away with the calculator.

I am thinking of creating a new sheet with a number of cells that have a formula similar to this
=IF(AND(A2="Beavers",B2="Adult",C2="Booking"),E2,0)

But I want it to see Adult, Guest (Adult) and Catalogue as the same thing / and I want it to pick up Child and Guest (Child) as the same thing.

I have had a little play with the OR function but that didn't seem to work??? Can anyone help, or suggest a better way to do this?

Any questions or if more clarification of exactly what I'm trying to do please ask.

Thanks
Jason

**oldchippy** · 08-07-2009, 10:37 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**jason7579** · 08-07-2009, 10:58 AM

Good idea.

Test sheet attached. The data I am working on is at the top, and the red table underneath would be the kind of thing I am hoping to acheive. Only problem being I want it to see Guest (Adult) and Catalogue as an Adult field, I thought I could use *'s e.g. =IF(AND(A5="Beavers",B5="*Adult*",C5="Booking"),E5,0)

But that doesn't work.

I'm finding it hard to explain but I hope you can see what I want.

Thanks Whizbang I'll try that as soon as I have a chance (probably next week)

**oldchippy** · 08-07-2009, 04:02 PM

Hi,

Does this help?

**Whizbang** · 08-07-2009, 10:49 AM

If you highlight the columns and then name them, you could use MS Query to run a query with the desired criteria then do sum, avg etc.

Create a second sheet in your workbook.

Go to "Data" -> "Import External Data" -> "New Database Query"
Select "Excel Files*"
Navigate to and select your file
Select the above named table
Click "View"->Criteria
Drag and drop the column names you want to test for criteria into the criteria area. (In this case "Branch", "User Type" and "Session Type"
In the Value row, enter the desired criteria. You can enter formulas, but here I think simple quotes around the desired text value is sufficient
Next, drag the desired column names into the white area below the criteria section. This is the output section. Anything you put in here will be outputted once the above conditions are met.
Select a column and click "Records" -> "Edit Column"
This will allow you to do sum, avg, count, min, max, etc depending on the data type.
Do this to any/all columns as desired
Click "File" -> "Return Data to Microsoft Excel"
It will then ask you to choose the desitination cel/range. You can also select from behavior options (Auto-refresh, column insertation, format preservation, etc.)

Simply do the same for each set of criteria you want to run. This will generate the desired totals/lists/results you seem to be asking for.