Countifs with multiple criteria and ranges within single column?

Hi all,

Having some trouble with this one.

I have a large set of data, all within one column (roughly 100k lines). And I am trying to come up with a formula to see if a specific cell value exists between two other specific cell values in that column. Whether or not it returns a true, yes, a number, or whatever is of little importance however.

My problem is since I have multiple criteria all within the same column and the range in between each instance is different, it's a bit of a puzzle to me. Anyone have an idea?

See attachment for an example (check both tabs).

Thank you,

Edit: I noticed in the mock formula I had in there I listed F2, it should of said F3.

Edited again: Please see post below with updated information

Is the order of the names the same on both sheets.

Incidentally would you edit your location to something meaningful. It often helps when we need to consider regional settings of date formats..etc.

Originally Posted by Richard Buttrey

Is the order of the names the same on both sheets.

Incidentally would you edit your location to something meaningful. It often helps when we need to consider regional settings of date formats..etc.

Heh, I never noticed my location listed as "yesterday", but that's fixed. To answer your first question, no - they are not in order.

Edited: I thought I could put them in order - but alas, they are not, and cannot be.

Perhaps this in B2
=COUNTIF(INDEX('Data Sample'!A:A,MATCH(A2&"",'Data Sample'!A:A,0)+1):INDEX('Data Sample'!A:A,IF(A3="",COUNTA('Data Sample'!A:A),MATCH(A3,'Data Sample'!A:A,0)-1)),$F$3)>0
and copy down?

This actually works for the test that I supplied, and is a very wise response. Next problem though that I failed to realize until my prior message - the names on the second sheet are not in the same order as the previous sheet, so my original thought, and your solution will not work for the application.

I have made some changes to the sheet, and extended the data amount as requested by another post and added it in this comment.

Basically - the formula will need to search in the data tab for the specified name (All begin with variations of "XX_","XXX_","XXXX_") and see if the lookup value of "B" (from the example sheet) exists until the next instance of "XX*". No other form of "XX*" will exist in the columns besides the names. And then return the result of either True or False.

Now, I am aware the test sheet has them in order, but the actual data is not.

Originally Posted by Skiptomylou

This actually works for the test that I supplied, and is a very wise response. Next problem though that I failed to realize until my prior message - the names on the second sheet are not in the same order as the previous sheet, so my original thought, and your solution will not work for the application.

I have made some changes to the sheet, and extended the data amount as requested by another post and added it in this comment.

Basically - the formula will need to search in the data tab for the specified name (All begin with variations of "XX_","XXX_","XXXX_") and see if the lookup value of "B" (from the example sheet) exists until the next instance of "XX*". No other form of "XX*" will exist in the columns besides the names. And then return the result of either True or False.

Now, I am aware the test sheet has them in order, but the actual data is not.

That was why I asked you in #2 if the names were in order.
Nevertheless the file you've attached still has the names on each sheet in the same order, i.e. Steve follows john and Mike follows Steve etc. with no others in between.

If as you say above the names are not in order thhe please supply a file that represents your data.
We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

Yes, but it wasn't clear what you meant by 'in order'

That could have meant the names were in alphabetical order, or that the names on the two lists were not in alphabetical order but were in the same relative positions in the two lists.

We don't expect you to upload workbooks with sensitive data but we do like you to anonymise your data keeping faithful to any formatting or string lengths etc and upload at representative workbook. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Please don't get critical and argumentative. None of us are paid for doing this which we do because we genuinely like to help people discover Excel, and we don't welcome the sort of response you've given.

Just solved this with a few helper columns:
In B:

Formula:

Please Login or Register  to view this content.

In C:

Formula:

Please Login or Register  to view this content.

In D:

Formula:

Please Login or Register  to view this content.

In E:

Formula:

Please Login or Register  to view this content.

Was able to consolidate in the actual worksheet to just a one formula. Large combination of all those together, but it works. Thanks to everyone who supplied feedback.

Originally Posted by Skiptomylou

You receive responses in the same fashion that you submit your questions - nothing more, nothing less.

In fairness, I don't welcome the sort of results or questions you have supplied, and would happily do without the initial "critical" responses.

You have my sample workbook, albeit 1/1500th the size, that represents the data in the true workbook. If you want to help, great, and I appreciate it. If not, that's alright too. But understand, I will always respond in kind.

Thanks again.

crit·i·cal
/ˈkridək(ə)l/
adjective
1. Inclined to find fault or to judge with severity, often too readily.

Just a minute. Where have I been critical?
And precisely what, in anything I have supplied, do you find unwelcome?

I still detect an element of truculence in your response. I don't expect to see you continue in this vein.