Multiple Row and Column Criteria

[Punweet Singh]

Hi Everyone!

Your advice and support here would really go a long long way. I will try and explain my issue below as best as possible

Refer to Sample Sheet - Google Sheets - create and edit spreadsheets online, for free.

The data is Range A14:I36 will be copied daily into a this sheet. I have added some basic macros in order to retrieve the data in J14:AL36. Where i need your help is populating the data in D5:J10

For example - In cell D5 i need to be able to count all the cells from column K14:K36 that is equal to "6" or ">0" BUT it should also fit the criteria that the remaining values in row L14:AL14 is blank or "0" A similar situation follows for all the remaining cells in D5:J10

it is possible that a customer has ordered a Indian water 6 pack and also a grapefruit tonic water 12 pack in which case that will count in cell C9 (Other).

I hope this wasn't too confusing and ill be happy to try and answer any queries. thank you !

 

[Joe4]

Is this an Excel question or a Google Sheets question?

 

[Punweet Singh]

Is this an Excel question or a Google Sheets question?

I've attached the sample sheet in order to help explain things a bit better, but this would definitely be an Excel question.

 

[Joe4]

OK, I was just asking because of the reference to Google Sheets in your link:

Refer to Sample Sheet - Google Sheets - create and edit spreadsheets online, for free.

Unfortunately, I am not able to download things from the internet on my work computer, and those sites are blocked for me.
I wish I was able to see your sheet, as I think I would need to see it to understand your question.
Note that there are tools available that allow you to post your Excel images directly in your post: XL2BB - Excel Range to BBCode

 

[Punweet Singh]

I've attached the sample sheet in order to help explain things a bit better, but this would definitely be an Excel question.

I have quickly attached a screenshot in case that helps. thanks for the input. i will try to incorporate it into the link shared by you!

 

[Joe4]

Yes, that would be helpful if you could so that, as we can copy and paste out of that, which would allow us to recreate your data set on our side.
In addition to this, if you could let us your expected results, based on the sample data provided. That way we can make sure that we create formulas that return the values that you are looking for.

 

[Punweet Singh]

Yes, that would be helpful if you could so that, as we can copy and paste out of that, which would allow us to recreate your data set on our side.
In addition to this, if you could let us your expected results, based on the sample data provided. That way we can make sure that we create formulas that return the values that you are looking for.

I have pasted below the excel images as requested. I have manually filled out the data in range D5:J9

You will notice 7 different type of product packs (D3:J3). I need to be able to take the data from (K14:AL36) and automatically plug it into the range D5:J9.
Example - If a customer has ordered a 6 Pack Indian Tonic Water and nothing else, then D5 should be added by "1" Therefore i have been trying to find a formula that will count all the "6's" in column (K14:K36) while also making sure all other cells in row (L14:AL14 are blank or "0")

The other issue which i am facing is that in case a customer orders more than one sku, meaning more than one cell in row (K14:AL14) is ">0" how can i display that information

Sample Sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
3	Total Boxes		Qty	Indian Tonic Water	Grapefruit Tonic Water	Elderflower Tonic Water	Ginger Ale	Club Soda	Tonic Mix Pack	Variety Pack
4
5	6 PACK		13	6	3	0	3	0	0	1
6	12 PACK		6	3	0	0	2	0	0	1
7	18 PACK		0	0	0	0	0	0	0	0
8	24 PACK		3	0	1	0	0	1	0	0
9	>24 PACK		1
10			23
11
12	S.No	Order ID			Customer Name			Price	Details	Pack Size	Indian Tonic Water				Grapefruit Tonic Water				Elderflower Tonic Water				Ginger Ale				Club Soda				Tonic Mix Pack				Variety Pack
13											6 Pack	12 Pack	24 Pack	Other	6 Pack	12 Pack	24 Pack	Other	6 Pack	12 Pack	24 Pack	Other	6 Pack	12 Pack	24 Pack	Other	6 Pack	12 Pack	24 Pack	Other	6 Pack	12 Pack	24 Pack	Other	6 Pack	12 Pack	24 Pack	Other
14		406-2845851-3356304			Aditya Bedre			495	JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN	6	0	0	0		0	0	0		0	0	0		0	0	0	0	0	0	0		0	0	0		6	0	0
15		171-4266266-2249955			Adinarayana Kadiyam			912	JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN	12	0	12	0		0	0	0		0	0	0		0	0	0	0	0	0	0		0	0	0		0	0	0
16		171-4191011-9665114			Kandasami kanjivaram			480	JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN	6	6	0	0		0	0	0		0	0	0		0	0	0	0	0	0	0		0	0	0		0	0	0
17		408-7334378-4640303			Joe Lawrence C			480	JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN	6	6	0	0		0	0	0		0	0	0		0	0	0	0	0	0	0		0	0	0		0	0	0
18		403-8641140-8953965			Eleonora Dimitrova			1836	JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN,JFGP24-MFN	24	0	0	0		0	0	24		0	0	0		0	0	0	0	0	0	0		0	0	0		0	0	0
19		171-4867378-1167551			Suhail Khan			510	JFGA06-MFN,JFGA06-MFN,JFGA06-MFN,JFGA06-MFN,JFGA06-MFN,JFGA06-MFN	6	0	0	0		0	0	0		0	0	0		6	0	0	0	0	0	0		0	0	0		0	0	0
20		406-7160698-5712346			Umang Sejpal			510	JFGA06-MFN,JFGA06-MFN,JFGA06-MFN,JFGA06-MFN,JFGA06-MFN,JFGA06-MFN	6	0	0	0		0	0	0		0	0	0		6	0	0	0	0	0	0		0	0	0		0	0	0
21		403-7440420-5567543			Karishma Singh			480	JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN	6	6	0	0		0	0	0		0	0	0		0	0	0	0	0	0	0		0	0	0		0	0	0
22		402-8733909-4077128			Prabhjot kaur			510	JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN	6	0	0	0		6	0	0		0	0	0		0	0	0	0	0	0	0		0	0	0		0	0	0
23		404-1185003-2441945			Prakash Panigrahi			480	JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN	6	6	0	0		0	0	0		0	0	0		0	0	0	0	0	0	0		0	0	0		0	0	0
24		406-1518571-0755554			Abhishek Kumar			480	JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN	6	6	0	0		0	0	0		0	0	0		0	0	0	0	0	0	0		0	0	0		0	0	0
25		171-5087906-2443528			Archana Priyadarshini			1005	JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFGP06-MFN,JFVP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFVP06-MFN,JFGP06-MFN	12	0	0	0		6	0	0		0	0	0		0	0	0	0	0	0	0		0	0	0		6	0	0
26		171-4701976-8167520			Rama Voruganti			480	JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN	6	6	0	0		0	0	0		0	0	0		0	0	0	0	0	0	0		0	0	0		0	0	0
27		404-2437364-0356336			Lajju Kapil Paleja			1620	JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN,JFCS24-MFN	24	0	0	0		0	0	0		0	0	0		0	0	0	0	0	0	24		0	0	0		0	0	0
28		407-1716371-6757100			Nikhil P			969	JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS	12	0	0	0		0	0	0		0	0	0		0	12	0	0	0	0	0		0	0	0		0	0	0
29		402-2398151-3680340			naresh N Runwal			990	JFTW06-MFN,JFGA06-MFN,JFGA06-MFN,JFTW06-MFN,JFGA06-MFN,JFTW06-MFN,JFTW06-MFN,JFGA06-MFN,JFTW06-MFN,JFGA06-MFN,JFGA06-MFN,JFTW06-MFN	12	6	0	0		0	0	0		0	0	0		6	0	0	0	0	0	0		0	0	0		0	0	0
30		171-2257553-8514750			Neha Prasada			510	JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN	6	0	0	0		6	0	0		0	0	0		0	0	0	0	0	0	0		0	0	0		0	0	0
31		402-6932901-1270702			Chirag Ashar			969	JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS	12	0	0	0		0	0	0		0	0	0		0	12	0	0	0	0	0		0	0	0		0	0	0
32		405-4912835-1889907			Minu nayar			1824	JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN	24	0	24	0		0	0	0		0	0	0		0	0	0	0	0	0	0		0	0	0		0	0	0
33		407-4570813-9046729			Bhavna Dhawan			912	JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN,JFTW12-MFN	12	0	12	0		0	0	0		0	0	0		0	0	0	0	0	0	0		0	0	0		0	0	0
34		408-8834585-7229963			Virendra Swarup			3000	JFGP06-MFN,JFGP06-MFN,JFGA06-MFN,JFGA06-MFN,JFGP06-MFN,JFGA06-MFN,JFGP06-MFN,JFGA06-MFN,JFTW06-MFN,JFTW06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFTW06-MFN,JFGA06-MFN,JFTW06-MFN,JFGP06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFGA06-MFN,JFTW06-MFN,JFGP06-MFN,JFGA06-MFN,JFTW06-MFN,JFTW06-MFN,JFGA06-MFN,JFGA06-MFN,JFGA06-MFN,JFTW06-MFN,JFGA06-MFN,JFGP06-MFN,JFGA06-MFN,JFGP06-MFN,JFGP06-MFN,JFTW06-MFN	36	12	0	0		12	0	0		0	0	0		12	0	0	0	0	0	0		0	0	0		0	0	0
35		171-1282277-2662738			Nekzad Parabia			510	JFGA06-MFN,JFGA06-MFN,JFGA06-MFN,JFGA06-MFN,JFGA06-MFN,JFGA06-MFN	6	0	0	0		0	0	0		0	0	0		6	0	0	0	0	0	0		0	0	0		0	0	0
36		404-4767443-0451519			Snehlatha Kothari			510	JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN	6	0	0	0		6	0	0		0	0	0		0	0	0	0	0	0	0		0	0	0		0	0	0
Sheet1

Cell Formulas
Range	Formula
C5	C5	=COUNTIF(J14:J289,"6")
C6	C6	=COUNTIF(J14:J290,"12")
C7	C7	=COUNTIF(J14:J291,"18")
C8	C8	=COUNTIF(J14:J292,"24")
C9	C9	=COUNTIF(J14:J292,">24")
C10	C10	=SUM(C5:C9)
J14:J36	J14	=LEN(TRIM(I14))-LEN(SUBSTITUTE(TRIM(I14),",",""))+1
K14:K36	K14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFTW06-MFN","")))/LEN("JFTW06-MFN")
L14:L36	L14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFTW12-MFN","")))/LEN("JFTW12-MFN")
M14:M36	M14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFTW24-MFN","")))/LEN("JFTW24-MFN")
O14:O36	O14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFGP06-MFN","")))/LEN("JFGP06-MFN")
P14:P36	P14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFGP12-MFN","")))/LEN("JFGP12-MFN")
Q14:Q36	Q14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFGP24-MFN","")))/LEN("JFGP24-MFN")
S14:S36	S14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFEF06-MFN","")))/LEN("JFEF06-MFN")
T14:T36	T14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFEF12-MFN","")))/LEN("JFEF12-MFN")
U14:U36	U14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFEF24-MFN","")))/LEN("JFEF24-MFN")
W14:W36	W14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFGA06-MFN","")))/LEN("JFGA06-MFN")
X14:X36	X14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFGA12-MFN","")))/LEN("JFGA12-MFN")
Y14:Y36	Y14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFGA24-MFN","")))/LEN("JFGA24-MFN")
AA14:AA36	AA14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFCS06-MFN","")))/LEN("JFCS06-MFN")
AB14:AB36	AB14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFCS12-MFN","")))/LEN("JFCS12-MFN")
AC14:AC36	AC14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFCS24-MFN","")))/LEN("JFCS24-MFN")
AE14:AE36	AE14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFTM06-MFN","")))/LEN("JFTM06-MFN")
AF14:AF36	AF14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFTM12-MFN","")))/LEN("JFTM12-MFN")
AG14:AG36	AG14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFTM24-MFN","")))/LEN("JFTM24-MFN")
AI14:AI36	AI14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFVP06-MFN","")))/LEN("JFVP06-MFN")
AJ14:AJ36	AJ14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFVP12-MFN","")))/LEN("JFVP12-MFN")
AK14:AK36	AK14	=(LEN(I14)-LEN(SUBSTITUTE(I14,"JFVP24-MFN","")))/LEN("JFVP24-MFN")

 

[Joe4]

OK, I think I understand what you are after.

Here are the formulas for the first few cells. You can see the pattern, and continue on with the rest:
In cell D5: =SUMPRODUCT(--($J$14:$J$36=K$14:K$36),--(K$14:K$36<>0))
In cell D6: =SUMPRODUCT(--($J$14:$J$36=L$14:L$36),--(L$14:L$36<>0))

I highlighted in red the part of the formula that changes for each one.

Does that give you what you are looking for?

 

[Punweet Singh]

Thanks Joe! that worked beautifully. i have my expertise in many fields but excel is definitely not one of them. Though I noticed an issue that arose when using your formula in a couple situations. I have attached a slightly edited sheet below. Let me try explaining this better.

In the earlier excel sheet on the basis of which you provided the formula, i created 4 columns for each product (K14:N14), (O14:R14) etc. your formula worked fine but in majority of cases but was giving an error in a couple places. To give you an example, consider row 14 and the details in cell I14. In cases where a customer orders multiple qtys of the same product, the product name in cell I14 would multiply accordingly. In this case, since i was calculating the name of the pack size "JFVP06-MFN" it was placing the value in cell "AI14" but since the qty is 12, it should be placed in cell "AJ14" Because of this, with the formula you provided, it would increase the count in cell J5 instead of J6

Since then, in the new sheet below, i have erased the 4 columns and kept only one for each. By calculating the times the first four characters in below attached excel sheet cell I12 is mentioned, i am getting the correct pack size. I now require the data from the range K12:Q28 to be filled out in range D4:K7

The second criteria that i originally posted was if a customer orders more than one sku, meaning cell I12 has more than one product name and (K12:Q12) would include multiple cells with values. In this case the addition of the pack size should only happen in the new range K4:K7

I do hope this was more clear and your expertise would really help a lot!

Copy of Copy of Sample Sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3	Total Boxes		Qty	Indian Tonic Water (TW)	Grapefruit Tonic Water (GP)	Elderflower Tonic Water (EF)	Ginger Ale (GA)	Club Soda (CS)	Tonic Mix Pack TM	Variety Pack (VP)	Other
4	6 PACK		13	3	3	0	1	2	0	4	0
5	12 PACK		3	0	0	0	1	0	0	1	1
6	18 PACK		0	0	0		0	0	0	0	0
7	24 PACK		1	0	0	0	0	0	0	1	0
8
9
10
11	S.No	Order ID			Customer Name			Price	Details	Pack Size	Indian Tonic Water	Grapefruit Tonic Water	Elderflower Tonic Water	Ginger Ale	Club Soda	Tonic Mix Pack	Variety Pack
12		405-0150618-2341923			Purnima Kamath		VP12	990	JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN	12	0	0	0	0	0	0	12
13		407-0221860-8517964			Basava prabhu Nyayakar		VP6	495	JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN	6	0	0	0	0	0	0	6
14		407-0630991-5026723			SHIVANGI CHATURVEDI		OTHER12	990	JFTW06-MFN,JFTW06-MFN,JFGP06-MFN,JFTW06-MFN,JFTW06-MFN,JFGP06-MFN,JFTW06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFTW06-MFN	12	6	6	0	0	0	0	0
15		405-8216363-0490717			Jean Jacques boulain		TW6	480	JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN	6	6	0	0	0	0	0	0
16		403-7211994-8218717			KABIR talwar		GA12	969	JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS,JFGA12-MFNS	12	0	0	0	12	0	0	0
17		406-2004376-9475514			AMIT DOL		CS6	450	JFCS06-MFN,JFCS06-MFN,JFCS06-MFN,JFCS06-MFN,JFCS06-MFN,JFCS06-MFN	6	0	0	0	0	6	0	0
18		402-1061077-8009155			Aishwarya Namjoshi		GP6	510	JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN	6	0	6	0	0	0	0	0
19		405-7878356-9350725			Amit Singh		VP24	1880	JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN,JFVP12-MFN	24	0	0	0	0	0	0	24
20		171-7040571-8627524			Ankur Motiwal		GP6	510	JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN	6	0	6	0	0	0	0	0
21		406-9563233-0156333			R RAVI PRAKASH		CS6	450	JFCS06-MFN,JFCS06-MFN,JFCS06-MFN,JFCS06-MFN,JFCS06-MFN,JFCS06-MFN	6	0	0	0	0	6	0	0
22		402-6373499-4922765			Antarip Pradhan		GA6	510	JFGA06-MFN,JFGA06-MFN,JFGA06-MFN,JFGA06-MFN,JFGA06-MFN,JFGA06-MFN	6	0	0	0	6	0	0	0
23		171-0088217-9020313			Sreenivas Reddy		TW6	480	JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN	6	6	0	0	0	0	0	0
24		404-7996707-0089156			Jaspal Singh		VP6	495	JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN	6	0	0	0	0	0	0	6
25		408-1854583-6069912			P.S.DAS		VP6	495	JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN	6	0	0	0	0	0	0	6
26		171-1821784-0002742			Ankur Suhag		TW6	480	JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN,JFTW06-MFN	6	6	0	0	0	0	0	0
27		407-0596829-9425923			Viral Chopra		GP6	510	JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN,JFGP06-MFN	6	0	6	0	0	0	0	0
28		407-2066024-4109906			Cupela Consulting		VP6	495	JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN,JFVP06-MFN	6	0	0	0	0	0	0	6
Sheet1

Cell Formulas
Range	Formula
D4	D4	=SUMPRODUCT(--($J$12:$J$134=K$12:K$134),--(K$12:K$134<>0))
C4	C4	=COUNTIF(J12:J303,"6")
C5	C5	=COUNTIF(J12:J304,"12")
C6	C6	=COUNTIF(J12:J305,"18")
C7	C7	=COUNTIF(J12:J306,"24")
J12:J28	J12	=LEN(TRIM(I12))-LEN(SUBSTITUTE(TRIM(I12),",",""))+1
K12:K28	K12	=(LEN(I12)-LEN(SUBSTITUTE(I12,"JFTW","")))/LEN("JFTW")
L12:L28	L12	=(LEN(I12)-LEN(SUBSTITUTE(I12,"JFGP","")))/LEN("JFGP")
M12:M28	M12	=(LEN(I12)-LEN(SUBSTITUTE(I12,"JFEF","")))/LEN("JFTEF")
N12:N28	N12	=(LEN(I12)-LEN(SUBSTITUTE(I12,"JFGA","")))/LEN("JFGA")
O12:O28	O12	=(LEN(I12)-LEN(SUBSTITUTE(I12,"JFCS","")))/LEN("JFCS")
P12:P28	P12	=(LEN(I12)-LEN(SUBSTITUTE(I12,"JFTM","")))/LEN("JFTM")
Q12:Q28	Q12	=(LEN(I12)-LEN(SUBSTITUTE(I12,"JFVP","")))/LEN("JFVP")

 

[Joe4]

I am sorry, but I am utterly confused by what you want now.
Perhaps you could walk me through an actual example based on the data you have posted, telling me what value you want in the upper section, and show me exactly how you arrive at that value.