Trying to combine Vlookup and Countif functions (and failing!)

[kickingk100]

Hi,

I have two sheets of data - the first (sheet1) is a list of activities done by schools (many with multiple entries per school), and in this sheet I've included two columns for two specific activity types that I've put a Y in if the activity corresponds with this type. The second sheet is a smaller list of school addresses with postcodes that I want to get data on (sheet2).

In the second sheet I have successfully done an X lookup to identify the schools that are in the master sheet that I want to get data on. I would then like to write a statement that says "if the postcode in column L in sheet2 is a match to the postcode in column F in sheet 1, then I would like to count each row in sheet1 where this postcode appears and there is a "Y" in column I.

I've tried nesting a vlookup in a countif statement to do this but I'm a complete novice in excel formula and I can't get it to work (I tried =countif(=VLOOKUP(L12,Sheet1!F),('Sheet1'!I:I, "Y"). Just wondered if anyone could help me?

Thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


It would also help greatly to have a small set of sample dummy data that we can use to test with.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[kickingk100]

Thank you for your reply and your suggestions. I've updated my details but unfortunately I can't add the add-in to my Windows laptop as it's restricted, and I've just tried to add it to my macbook but Excel keeps crashing and it's now telling me I have to reinstall in from the app store so I think it's going to take longer to sort that and create the sample dummy data than it is to just do the count manually for all the data items!

Thanks anyway for taking the time to reply

 

[Peter_SSs]

Thanks for updating your details.

If you do want to persist, as a lesser option you can just copy/paste from Excel and tell us the range - we can at least copy from that
eg J7:L10

Budget		Var
120,000​		30,000​
65,000​		-65,000​
60,000​		-60,000​

or paste an image - but we cannot copy from that