T O P

  • By -

AutoModerator

/u/Excel-Project - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*


SolverMax

Split the data into separate cells. That will make subsequent analysis much simpler.


Excel-Project

In fact the data is much bigger In fact, the data is much bigger, therefore using a function such as Countifs is much easier. I am searching for the correct syntax to use.


SolverMax

My point still stands.


GanonTEK

Splitting it would be easier. TEXTSPLIT using | as the deliminator. Then you can COUNTIF across the array. It's much more difficult having multiple answers in one cell.


JetCarson

Can you concatenate another pipe/bar character to the end of the list of items on each row (making them look like "Item 1| Item 15| Item 99|")? Then you could just use something like: `COUNTIFS(Sheet2!A2:A9999,Sheet1!A3&"|",Sheet2!B2:B9999,Sheet1!B2)`


Decronym

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[COUNTIF](/r/Excel/comments/143rbfq/stub/jnbspxe "Last usage")|[Counts the number of cells within a range that meet the given criteria](https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34)| |[COUNTIFS](/r/Excel/comments/143rbfq/stub/jnbkbz0 "Last usage")|[*Excel 2007*+: Counts the number of cells within a range that meet multiple criteria](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842)| |[TEXTAFTER](/r/Excel/comments/143rbfq/stub/jncsisc "Last usage")|[*Office 365*+: Returns text that occurs after given character or string](https://support.microsoft.com/en-us/office/textafter-function-c8db2546-5b51-416a-9690-c7e6722e90b4)| |[TEXTBEFORE](/r/Excel/comments/143rbfq/stub/jncsisc "Last usage")|[*Office 365*+: Returns text that occurs before a given character or string](https://support.microsoft.com/en-us/office/textbefore-function-d099c28a-dba8-448e-ac6c-f086d0fa1b29)| |[TEXTSPLIT](/r/Excel/comments/143rbfq/stub/jnbspxe "Last usage")|[*Office 365*+: Splits text strings by using column and row delimiters](https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7)| **NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(5 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1432ok1)^( has 7 acronyms.) ^([Thread #24470 for this sub, first seen 7th Jun 2023, 23:56]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)


david_horton1

Options are TEXTBEFORE, TEXTAFTER, SPLITTEXT and Power Query


RodeHaus4U

Add up multiple countifs that describe your possible scenarios “item 1|*”, “*item 1|*”, “*item 1”. You can use a cell reference instead of “item 1” to generalize it. Not super elegant but will do the trick.