total based on prodcuts name

[Manoj K]

I have an excel file, which contains name of product with different packing structures. Against each product month wise production is written in another columns. Is there any formula to find out monthly production total based on products two or three words from left side.

thank you in advance

 

[Manoj K]

There were some mistake in product identification for total. Revised product based total is given. I would appreciate if anyone can give formula for monthly total based on product name

 

[awoohaw]

Try this:

=SUM(ISNUMBER(FIND($A50,$A$46:$A49))*(B$46:B49))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
45	SAP NAME	Monthly Budget	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24
46	ALMOTRIPTAN FOBV	71	24	83	42	65	78	44	46	41	39	19	85	45
47	ALMOTRIPTAN GNCJ	38	13	17	21	45	63	78	20	79	79	37	85	54
48	ALMOTRIPTAN XYAD	20	26	86	85	35	22	99	34	21	32	86	94	26
49	ALMOTRIPTAN CUDB	93	26	87	96	40	81	62	75	64	68	55	83	92
50	ALMOTRIPTAN	222	89	273	244	185	244	283	175	205	218	197	347	217
51	APIFEN APMW	45	50	97	47	38	33	39	88	59	33	29	56	82
52	APIFEN DFIY	25	98	94	75	99	15	93	73	87	46	28	17	70
53	APIFEN BXEA	20	62	62	52	22	69	89	11	11	27	88	86	17
54	APIFEN ROYI	90	41	79	90	36	28	17	77	34	71	34	98	71
55	APIFEN DFGD	57	95	63	44	50	76	69	47	88	42	83	79	80
56	APIFEN QTCM	16	36	95	76	22	80	38	12	57	15	37	81	86
57	APIFEN	253	382	490	384	267	301	345	308	336	234	299	417	406
58	APRAZOLE DKMA	53	26	97	99	89	12	48	73	79	29	77	71	35
59	APRAZOLE CKXJ	36	38	81	92	41	11	32	33	20	79	69	92	29
60	APRAZOLE BVTF	48	52	20	23	36	32	86	34	19	64	57	82	14
61	APRAZOLE HPCH	66	68	52	36	65	52	48	22	82	33	74	63	81
62	APRAZOLE ZFCC	60	19	80	45	26	85	57	18	16	73	54	25	40
63	APRAZOLE ZUYK	51	86	62	61	62	88	56	99	93	59	88	36	57
64	APRAZOLE TLSN	60	42	56	86	55	91	66	16	20	56	19	57	67
65	APRAZOLE MNDN	67	15	38	17	89	80	84	74	80	23	40	30	98
66	APRAZOLE	441	346	486	459	463	451	477	369	409	416	478	456	421
67	ARTEFAN HUPE	58	53	46	49	46	16	72	89	38	85	39	89	29
68	ARTEFAN DHFP	49	19	48	85	23	93	31	56	57	21	81	82	26
69	ARTEFAN PCCO	47	82	62	46	20	18	85	71	81	20	87	43	31
70	ARTEFAN MZHA	13	63	32	82	21	61	32	44	15	84	23	40	30
71	ARTEFAN RKAA	13	42	53	72	43	55	80	46	11	82	97	35	71
72	AFLOTIN ZYEG	18	73	87	62	82	78	51	96	43	46	98	60	48
73	AFLOTIN THZE	40	82	34	76	73	13	71	43	30	61	89	63	18
74	AFLOTIN OQLN	81	78	87	44	77	66	43	47	17	59	61	50	77
75	AFLOTIN TUYG	19	71	83	59	50	87	34	58	78	31	63	81	71
76	AFLOTIN LPHS	35	86	55	48	93	12	31	47	64	76	11	74	55
77	AFLOTIN YWRN	59	47	18	56	78	74	81	42	48	34	18	72	31
78	AFLOTIN BTTX	92	17	26	64	21	29	83	72	15	75	37	35	65
79	AFLOTIN	344	454	390	409	474	359	394	405	295	382	377	435	365
Sheet1

Cell Formulas
Range	Formula
A50,A79,A66,A57	A50	=TEXTBEFORE(A49," ")
B50:N50,B79:N79,B66:N66,B57:N57	B50	=SUM(ISNUMBER(FIND($A50,$A$46:$A49))*(B$46:B49))

Sorry the values are not matching yours. I'm not going to recreate the scenario entirely from scratch. If you need it exact, then please use the xl2bb add in (link below) or share a link from an reputable file sharing site.

 

[Manoj K]

I tried above formula, but not getting. Actually I had also done a mistake will requesting the formula. My data file will be continues without any row gap as given below. I need total of all matching products in seperate row below columns B,C,D, E, etc (like excel subtotal function) for matching word. eg One total for all Aflotin, another total for Almotriptan, another total for Apcycline, Aprifen, Aprazole, etc. Please help.

Actually I want result like below

 

[awoohaw]

I tried above formula, but not getting.

What result are you getting? Is it an error? This doesn't help solve the question. Please give the error description or the "incorrect" result it gives, and what it should give? What is the formula you used (and what cell is it in)?



have two questions about your last post:

1. What is the formula you entered when you attempted the suggestion I gave?
(also give what cell address it is in).

2. You show one image with no subtotal line, and another with subtotal lines.
Are you asking for a macro that inserts new lines or asking for a pivot table on another worksheet?

Try inputting the suggestion I give into a new workbook or worksheet and see how it works with the data I have made up?


Below is the what I originally posted but with conditional formatting (except I could not "center" align column A header) to match your desired output format. (Note: the conditional formatting I'm using assumes your "category" name has no spaces in it. - you'll need to adjust accordingly if you have categories with spaces).

Please post a sample of your data using xl2bb (link below) or as a table (Images are not copyable data). I'm not going to type in all of your scenario - it is too time consuming and I think what I've given you should work. You just need to apply it to your data.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	SAP NAME	Monthly Budget	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24
2	ALMOTRIPTAN FOBV	71	24	83	42	65	78	44	46	41	39	19	85	45
3	ALMOTRIPTAN GNCJ	38	13	17	21	45	63	78	20	79	79	37	85	54
4	ALMOTRIPTAN XYAD	20	26	86	85	35	22	99	34	21	32	86	94	26
5	ALMOTRIPTAN CUDB	93	26	87	96	40	81	62	75	64	68	55	83	92
6	ALMOTRIPTAN	222	89	273	244	185	244	283	175	205	218	197	347	217
7	APIFEN APMW	45	50	97	47	38	33	39	88	59	33	29	56	82
8	APIFEN DFIY	25	98	94	75	99	15	93	73	87	46	28	17	70
9	APIFEN BXEA	20	62	62	52	22	69	89	11	11	27	88	86	17
10	APIFEN ROYI	90	41	79	90	36	28	17	77	34	71	34	98	71
11	APIFEN DFGD	57	95	63	44	50	76	69	47	88	42	83	79	80
12	APIFEN QTCM	16	36	95	76	22	80	38	12	57	15	37	81	86
13	APIFEN	253	382	490	384	267	301	345	308	336	234	299	417	406
14	APRAZOLE DKMA	53	26	97	99	89	12	48	73	79	29	77	71	35
15	APRAZOLE CKXJ	36	38	81	92	41	11	32	33	20	79	69	92	29
16	APRAZOLE BVTF	48	52	20	23	36	32	86	34	19	64	57	82	14
17	APRAZOLE HPCH	66	68	52	36	65	52	48	22	82	33	74	63	81
18	APRAZOLE ZFCC	60	19	80	45	26	85	57	18	16	73	54	25	40
19	APRAZOLE ZUYK	51	86	62	61	62	88	56	99	93	59	88	36	57
20	APRAZOLE TLSN	60	42	56	86	55	91	66	16	20	56	19	57	67
21	APRAZOLE MNDN	67	15	38	17	89	80	84	74	80	23	40	30	98
22	APRAZOLE	441	346	486	459	463	451	477	369	409	416	478	456	421
23	ARTEFAN HUPE	58	53	46	49	46	16	72	89	38	85	39	89	29
24	ARTEFAN DHFP	49	19	48	85	23	93	31	56	57	21	81	82	26
25	ARTEFAN PCCO	47	82	62	46	20	18	85	71	81	20	87	43	31
26	ARTEFAN MZHA	13	63	32	82	21	61	32	44	15	84	23	40	30
27	ARTEFAN RKAA	13	42	53	72	43	55	80	46	11	82	97	35	71
28	AFLOTIN ZYEG	18	73	87	62	82	78	51	96	43	46	98	60	48
29	AFLOTIN THZE	40	82	34	76	73	13	71	43	30	61	89	63	18
30	AFLOTIN OQLN	81	78	87	44	77	66	43	47	17	59	61	50	77
31	AFLOTIN TUYG	19	71	83	59	50	87	34	58	78	31	63	81	71
32	AFLOTIN LPHS	35	86	55	48	93	12	31	47	64	76	11	74	55
33	AFLOTIN YWRN	59	47	18	56	78	74	81	42	48	34	18	72	31
34	AFLOTIN BTTX	92	17	26	64	21	29	83	72	15	75	37	35	65
35	AFLOTIN	344	454	390	409	474	359	394	405	295	382	377	435	365
Sheet1

Cell Formulas
Range	Formula
B6:N6,B35:N35,B22:N22,B13:N13	B6	=SUM(ISNUMBER(FIND($A6,$A$2:$A5))*(B$2:B5))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A35	Expression	=ISNUMBER(FIND(" ",A2))=FALSE	text	NO
B2:N35	Expression	=ISNUMBER(FIND(" ",$A2))=FALSE	text	NO

 

[awoohaw]

HEre is a Power Query Pivot Table where a new column for just the text before " " in the SAP NAME is used as a category.
(Power Query Syntax follows).

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SAP NAME", type text}, {"Monthly Budget", Int64.Type}, {"May-23", Int64.Type}, {"Jun-23", Int64.Type}, {"Jul-23", Int64.Type}, {"Aug-23", Int64.Type}, {"Sep-23", Int64.Type}, {"Oct-23", Int64.Type}, {"Nov-23", Int64.Type}, {"Dec-23", Int64.Type}, {"Jan-24", Int64.Type}, {"Feb-24", Int64.Type}, {"Mar-24", Int64.Type}, {"Apr-24", Int64.Type}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter([SAP NAME], " "), type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted Text Before Delimiter",{"Text Before Delimiter", "SAP NAME", "Monthly Budget", "May-23", "Jun-23", "Jul-23", "Aug-23", "Sep-23", "Oct-23", "Nov-23", "Dec-23", "Jan-24", "Feb-24", "Mar-24", "Apr-24"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Text Before Delimiter", "Drug Category"}})
in
    #"Renamed Columns"

Here is the xl2bb of the sheet the pivot report is on, but it may not copy the pivot table functionality:

Book1
	A	B	C	D	E
1	Drug Category	SAP NAME	Sum of May-23	Sum of Jun-23	Sum of Jul-23
2	AFLOTIN	AFLOTIN BTTX	17	26	64
3		AFLOTIN LPHS	86	55	48
4		AFLOTIN OQLN	78	87	44
5		AFLOTIN THZE	82	34	76
6		AFLOTIN TUYG	71	83	59
7		AFLOTIN YWRN	47	18	56
8		AFLOTIN ZYEG	73	87	62
9	AFLOTIN Total		454	390	409
10	ALMOTRIPTAN	ALMOTRIPTAN CUDB	26	87	96
11		ALMOTRIPTAN FOBV	24	83	42
12		ALMOTRIPTAN GNCJ	13	17	21
13		ALMOTRIPTAN XYAD	26	86	85
14	ALMOTRIPTAN Total		89	273	244
15	APIFEN	APIFEN APMW	50	97	47
16		APIFEN BXEA	62	62	52
17		APIFEN DFGD	95	63	44
18		APIFEN DFIY	98	94	75
19		APIFEN QTCM	36	95	76
20		APIFEN ROYI	41	79	90
21	APIFEN Total		382	490	384
22	APRAZOLE	APRAZOLE BVTF	52	20	23
23		APRAZOLE CKXJ	38	81	92
24		APRAZOLE DKMA	26	97	99
25		APRAZOLE HPCH	68	52	36
26		APRAZOLE MNDN	15	38	17
27		APRAZOLE TLSN	42	56	86
28		APRAZOLE ZFCC	19	80	45
29		APRAZOLE ZUYK	86	62	61
30	APRAZOLE Total		346	486	459
31	ARTEFAN	ARTEFAN DHFP	19	48	85
32		ARTEFAN HUPE	53	46	49
33		ARTEFAN MZHA	63	32	82
34		ARTEFAN PCCO	82	62	46
35		ARTEFAN RKAA	42	53	72
36	ARTEFAN Total		259	241	334
Sheet4