Lookup code,check number of entries and check how many cells are greater than another row

Hi,
I am really hoping someone will know how to do the following. I have tried a few things but really struggling.

I have two worksheets and want to be able to lookup a cell reference from Sheet2 in Sheet 1 and then perform several calculations as follows:-

E.G

1. I want a formula in sheet1 column B1 that firstly looks up the code in Sheet1 A1 on Sheet 2 column A
2. Then checks that there is a certain number of entries in column B:G for that code e.g. 6. If there is not then it returns a blank but if there is then it goes to the next part of the calculation
2. I want to see how many cells are greater than the same column but a different row in Sheet2 e.g. Row 3, and give me a count of how many are, however, I need it to then skip a column e.g. B1 compared to B3, then D1 compared to D3 then F1 compared to F3

In the above scenario the formula would give the result 2 as two of the cells referenced are greater than row 3.

I hope that makes sense and I appreciate any help. I have attached a file as an example.

Thanks

Paul

I don't know whay it has taken 3 days to get a reply to this.... However, your explanation of Part 2 was a bit confusing... That said, is this what you want?

=SUMPRODUCT((Sheet2!$B$2:$G$4>3)*(Sheet2!$A$2:$A$4=Sheet1!$A$2))

If not, please provise SEVERAL examples of the desired results.

Probably the inadequate thread title.

Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No further help to be offered, please, until the OP complies with this request.

One way:

=IF(COUNT(Sheet2!B2:I2,"<>""")=8,SUMPRODUCT(--((Sheet2!B2:I2)*ISEVEN(COLUMN(Sheet2!$B$1:$I$1))>Sheet2!$B$7:$I$7)),"Insufficient")

I should have said... this relies on the rows being in the same order on both sheets (like your example). It thy're not, it will require modification.

maybe this... array entered.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.