CountIFS with Unique String

[Simon2001]

Hi

I'm trying to use a COUNTIFS on some data with multiple criteria. My source sheet has a list and each row has a different single or multiple code in the type column. On a separate sheet, I'm using the COUNTIFS to count and search the range for how many contain each unique code but I am stumped and its only counting the unique codes (single entry per cell) but I also want it to count them within in a cell with multiple code entries.

For example below, code 21-HR is counted twice but I want it to return 4 times as its also in two cells which have multiple values.

In the attached sheet, 21-HR is in rows 27 & 29 but also within rows 14 & 15.

The formula used is simply =COUNTIFS($B$4:$B$34,$C4) and for ease of review, I've left out the other criteria in the COUNTIFS.

Can anyone help?

 

[etaf]

try
=COUNTIFS($B$4:$B$34,"*"&$C4&"*")
that should count all occurrences

 

[Simon2001]

Thanks, I did try that and it does indeed catch them all but it also is catching anything that contains the code. For example for 2-HR search, it counts in 12-HR and also 2-HR

I cant seem to get it only to count the exact string. Maybe I need to use a better more unique coding system. Most rows will only ever have a single code but there will be instances were we need multiple codes in the same cell and I don't want to have to resort to 1 column per code option.

 

[etaf]

i thought i had seen Fluff, member post a correction to a post where this was an issue to one of these solutions to avoid that issue - BUT not sure where , I think it was a post of mine as well, but may have been on another forum
i'll have a look

meanwhile

and a bit messey , needing helper columns , which can be hidden , and off the top of my head , as an alternative
i played with lenght LEN() and substitute

but as you have 365 - you have textsplit()

then some helper columns - 1 for the number of possible entries

As they seem to have ", " between each cell
=TEXTSPLIT(A4,", ")

that will split all the entries , and then you can count without a wilcard

but i cant see exactly whats needed from the image - maybe a sample with XL2BB or a share

shift hours over midnight-etaf.xlsx
	A	B	C	D	E	F	G	H
1
2	Text	Text Split				count code on each row		Count Total
3
4	2-hr	2-hr				1		6
5	2-hr, 2-hr	2-hr	2-hr			2
6	2-hr, 12-hr	2-hr	12-hr			1
7	2-hr, 12-hr, 2-hrb, 2-hr	2-hr	12-hr	2-hrb	2-hr	2
Sheet4

Cell Formulas
Range	Formula
H4	H4	=COUNTIF(B4:E7,"2-hr")
B4,B7:E7,B5:C6	B4	=TEXTSPLIT(A4,", ")
F4:F7	F4	=COUNTIF(B4:E4,"2-hr")

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[etaf]

had a further play and used sumproduct - not sure if it fully working
but
=SUMPRODUCT(--(TEXTSPLIT(A4,", ")="2-hr"))

shift hours over midnight-etaf.xlsx
	A	B
1
2	Text	count sumproduct
3
4	2-hr	1
5	2-hr, 2-hr	2
6	2-hr, 12-hr	1
7	2-hr, 12-hr, 2-hrb, 2-hr	2
Sheet4

Cell Formulas
Range	Formula
B4:B7	B4	=SUMPRODUCT(--(TEXTSPLIT(A4,", ")="2-hr"))

 

[Simon2001]

Thanks for help, I can use this and add the helper columns as a solution, didn’t know about the text split formula so good to know that. I’ll have a play in morning.

Thanks for the help with this and providing so much information.

 

[etaf]

the sumproduct saves needing to use any helper columns i posted that later

 

[Simon2001]

Perfect, thanks. It works on my personal computer (I have 365) but not on work one (we are not up to 365, yet) and I have a bit of time so will use this approach once my work machine is on 365 as it will save me more time than having all the helpers.

Thanks for the help with this!

 

[etaf]

you are welcome

 

[shinigamilight]

U can try this.

=SUMPRODUCT(--(IFERROR(SEARCH(B1,$A$1:$A$4),0)>0))