If Statement checking range of cells

[Lockeitivity]

I'll try to explain my question as best as possible.
I have a spreadsheet of books sold at different discount levels. So colA = Discount level, colB = Title, ColC = Quantity sold.

The formula I'm trying to get is to return the quantity in ColC if ColA=.35 and ColB='examplebooktitle1'

I want the formula to scan a range of cells to match the criteria and when a match is found to return the value in ColC for that Row.

The report I receive is different month to month as different discounts are used each time, as well I'm using a MAC for this project and thus am not able to write this in VBA.

Thanks for any help,

Chris

[mojo249]

Chris

You could put the following formula in column D and then sum column D

=IF(AND($A2=35,$B2="examplebooktitle1"),$C2,"")

Dion

[Lockeitivity]

Dion, thank you for your suggestion, that is originally where I started.

Here is a little more information on what I'm doing. I pull reports out of an SQL query and put the results into my excel workbook. I am then trying to place this information into another sheet that I have already created which has separate columns for the different discount rates. From month to month the discounts returned on the SQL report will be different, sometimes a 35% or others a 10%. so locking in a row for a certain return.

So again I need Sheet 1, for discount level @ 10%, I need to pull from sheet 2 the quantity sold(ColC), IF discount(Sheet2!ColA) = .10 and the title(Sheet2!ColB) = "examplebooktitle1"

The formula would need to search the entire range of cells for a match where ColA and ColB both match and then if true would return for ColC in that row, if False it would only return 0.

Thus why I can't lock it in as you suggested to search only Row2.

Thanks,

Chris

[rylo]

Hi

Can you attach an example workbook so we can get a better idea of your structure.

Also, give an expected result for some of the sample data.

rylo

[Lockeitivity]

OK, I was able to create a sample report, based on what I am trying to accomplish, find that attached.

So the order in which this works is this:

1. I import data from an SQL query for a month into the Sheet 'SQL Month X'. I do these reports quarterly so there are 3 Sheets, one for each month.
2. I am then trying to pull this data into another report, Sheet labeled 'Month 1'. This sheet combines the SQL data as well as data from other spreadsheets.
3. So the main problem is that from month to month the SQL report is not consistent in the amount of data I receive, So what I am trying to do is write a formula that searches multiple cells for data. Saying basically if search range for Book Title(Col B) @ discount level (Col A) and if match is found then report Quantity sold (Col C).

4. This needs to be done for multiple titles at multiple discounts.

Again thanks for any help, I greatly appreciate it.

Chris

[rylo]

Hi

Has the data in SQL - Month 1!B:B (ie the title) been "doctored" at all, or is this how it comes in from the sql query? It is quite different in format from the data in SQL - Month 2.

rylo

[Lockeitivity]

The information you need is in Month 1, I adjusted the data as not to include any personal information. However this should not effect the formula.

Thanks

Chris