help with formula

[kennettz]

I have a query with an excel formula that I’m trying to create and think I’ve been looking at it too long now! and need help from the experts!
I want it to count the unique values when the two calculations in the following formula are combined.

For example there are 29 school names that fall within dates in tab called ‘master’ and 18 schools in the ‘end of placement’ tab totalling 47 – the answer I have, using the formula below, is correct. However, if we look at the names in both the master sheet and end of placements tabs there are duplicate school names. After taking out the duplicates, the answer should be 36. What do I need to add / remove from the formula to tell it count only the unique number of schools by name and not the sum?

 

[Peter_SSs]

Welcome to the MrExcel board!

"Prefer not to say" for your Office version in your profile is completely unhelpful.
You are asking for a formula to give your desired result but different functions are available in different Excel versions so how do we know which ones are available to you when you don't tell us your version?

It would also be helpful if you gave us a smallish set of sample data and the expected results with XL2BB and explained again in relation to that sample data.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[kennettz]

I have a query with an excel formula that I’m trying to create and think I’ve been looking at it too long now! and need help from the experts!
I want it to count the unique values when the two calculations in the following formula are combined.

For example there are 29 school names that fall within dates in tab called ‘master’ and 18 schools in the ‘end of placement’ tab totalling 47 – the answer I have, using the formula below, is correct. However, if we look at the names in both the master sheet and end of placements tabs there are duplicate school names. After taking out the duplicates, the answer should be 36. What do I need to add / remove from the formula to tell it count only the unique number of schools by name and not the sum?

View attachment 119052

Thank you for your response - i'm using 365 - apologies but I'm unable to upload a mini sheet (work based restrictions) - and the jpeg file is too large too despite compressing!

 

[Peter_SSs]

Thanks for the version information. However, without some sample data to set up a test workbook with some relevant data in the relevant layout I'm finding it very difficult to understand just what you have and where it is and therefore difficult to offer anything useful. Also, if your were able to provide us with some small sample data and explain again in relation to that data please also provide the actual formula (ie copy paste) rather than a picture of it which we cannot copy from.