SUMIFS help if there is a + sign

[K0st4din]

Hello, everyone,
I ask for your help to correct the formula, because I have a condition that is a little different and that's where my problem comes from.
I will attach a photo to see the columns I work with and respectively the end result, which gives me an error in adding the numbers when they are +
If we take BISO5 - the result should be = 360, but because in some places I have two words and two numbers, respectively, the end result is = 305, which is not true.
The formula I made is calculated, but when it is missing (or it is independent).
Please for some assistance how in the same table, to calculate correctly, if possible, of course!
Thank you in advance!

 

[KRice]

If you are interested in a helper cell approach, the following demonstrates how it could be done. Somewhere to the right of your main data table, two blocks of formulas are used to split the category codes cells (column J) and values cells (column P) into separate columns. The column headers refer to the item number to be extracted from those source cells. This sample assumes that up to six items might be included in the cell, but it can be extended further if needed. To stay below character limits on this post, I've included just the top 24 rows, but the helper formulas are extended down the entire length of your data block.

feb21.xlsx
	B		D						J						P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD										AN	AO	AP	AQ	AR
1	name		date						kkk						point		1	2	3	4	5	6	1	2	3	4	5	6											4/1/2020	10/30/2020	Dave Toni	mostri	pro
2	Dave Toni		5/19/2020						RUS10						15		RUS10						15	0	0	0	0	0														0	MOXI5
3	Dave Toni		10/20/2020						BISO5						10		BISO5						10	0	0	0	0	0														0	MOXI7
4	Dave Toni		10/20/2020						RUS10						15		RUS10						15	0	0	0	0	0														0	LEV5
5	Dave Toni		10/6/2020						BISO5						10		BISO5						10	0	0	0	0	0														0	LEV7
6	Dave Toni		9/21/2020						BISO5						10		BISO5						10	0	0	0	0	0														120	AMLF10
7	Dave Toni		8/17/2020						BISO5+AMLF10						10+15		BISO5	AMLF10					10	15	0	0	0	0														9	AMLF5
8	Dave Toni		5/11/2020						RUS10						15		RUS10						15	0	0	0	0	0														0	AMOL5/40
9	Dave Toni		5/20/2020						RUS10						15		RUS10						15	0	0	0	0	0														0	AMV5/160
10	Dave Toni		7/2/2020						BISO5						10		BISO5						10	0	0	0	0	0														350	BISO5
11	Dave Toni		10/5/2020						BISO5						10		BISO5						10	0	0	0	0	0														0	CANDF16
12	Dave Toni		10/5/2020						RUS10						15		RUS10						15	0	0	0	0	0														0	CLOP
13	Dave Toni		9/30/2020						BISO5						10		BISO5						10	0	0	0	0	0														0	COXI200
14	Dave Toni		10/7/2020						BISO5						0		BISO5						0	0	0	0	0	0														0	DOXA2
15	Dave Toni		10/6/2020						RUS10						15		RUS10						15	0	0	0	0	0														0	DOXA4
16	Dave Toni		9/16/2020						BISO5						10		BISO5						10	0	0	0	0	0														0	IND1.5SR
17	Dave Toni		10/22/2020						RUS10						15		RUS10						15	0	0	0	0	0														0	IRB150
18	Dave Toni		8/4/2020						BISO5+AMLF10						10+15		BISO5	AMLF10					10	15	0	0	0	0														0	IRB300
19	Dave Toni		43970						BISO5						10		BISO5						10	0	0	0	0	0														0	LERC10
20	Dave Toni		44032						BISO5						10		BISO5						10	0	0	0	0	0														0	LOSF50/12.5
21	Dave Toni		44091						RUS10						15		RUS10						15	0	0	0	0	0														0	NEBIF5
22	Dave Toni		44062						BISO5						10		BISO5						10	0	0	0	0	0														330	RUS10
23	Dave Toni		43966						BISO5						10		BISO5						10	0	0	0	0	0														0	VALCO160/12.5
24	Dave Toni		44133						BISO5						10		BISO5						10	0	0	0	0	0														0	VALF160
Sheet1

Cell Formulas
Range	Formula
R2:W24	R2	=TRIM(MID(SUBSTITUTE($J2,"+",REPT(" ",LEN($J2))),(R$1-1)*LEN($J2)+1,LEN($J2)))
X2:AC24	X2	=IFERROR(VALUE(TRIM(MID(SUBSTITUTE($P2,"+",REPT(" ",LEN($P2))),(X$1-1)*LEN($P2)+1,LEN($P2)))),0)
AQ2:AQ24	AQ2	=SUMPRODUCT(($D$2:$D$65>=$AN$1)*($D$2:$D$65<=$AO$1)*($B$2:$B$65=$AP$1)*($R$2:$W$65=$AR2)*($X$2:$AC$65))

 

[K0st4din]

Здравейте, благодаря ви много за съдействието. Напълно разбирам, че този знак + създава много проблеми. Разбира се, аз също съм склонен към някакъв макрос да свърша цялата тази работа, защото имам 28 работни листа и във всеки ще изпълнявам това действие поотделно. Също така ще тествам предложените формули, за да сравня резултатите. Що се отнася до това колко думи би могло да има и съответно точки, то е в пъти различно, но минимумът е 1, максимумът може да бъде 20.
Оставям въпроса си отворен, защото баща ми е починал и ще ми трябва известно време, за да се справя с гробищата, погребенията и всичко останало. Тогава ще пиша веднага. Благодаря ти много!

 

[K0st4din]

Hello, thank you very much for your cooperation. I fully understand that this + sign creates a lot of problems. Of course, I'm also prone to some macro to do all this work, because I have 28 worksheets and in each I will perform this action separately. I will also test the proposed formulas to compare the results. As for how many words there could be and respectively points, it is many times different, but the minimum is 1, the maximum can be 20.
I leave my question open because my father has died and it will take me some time to deal with cemeteries, funerals and everything else. Then I will write immediately. Thank you very much!

 

[KRice]

Thank you for the update. I'm sorry for your loss. When the time is right and you re-visit this, have a look at Post #21 where helper columns are used. You will probably need to move the columns very far to the right and expand them to two blocks of 20 columns each to accommodate the maximum number of category codes (the first set of 20 columns) and their corresponding values (the second set of 20 columns). I think this approach might work for you, but with 28 worksheets, a VBA solution is probably the preferred option.

 

[K0st4din]

Hello KRice,
in the case of the last post everything is as it should be, but it turns out that you are very right to be done with a macro, because, these formulas will load an awful lot of excel table and it in 28 worksheets.
There is only one detail about the formula in AQ
This $AP$1 must be a variable, ie $AP1, because after checking the given name on all words, then down continues with other names that will also be checked for points on given words. The idea is to compare $AP1, $AP2, $AP3 and so on. In this way, the table will do its calculations and will not fill the table with formulas that constantly load it
=SUMPRODUCT(($D$2:$D$65>=$AN$1)*($D$2:$D$65<=$AO$1)*($B$2:$B$65=$AP$1)*($R$2:$W$65=$AR2)*($X$2:$AC$65))
And the macro, if you can do it so that I can write in which worksheet to use it and accordingly be able to rearrange the columns and rows and we will test it.
Thank you very much for understanding my father and for your help!
I wish you good health and much happiness! See you soon!

 

[KRice]

OK, I assumed that your summary would be based on a query involving a date range, a single individual's name, and all of the category codes. And if you wanted a summary for a different individual, you could simply change the name in one cell ($AP$1). That would allow for a more compact summary table. However, if you plan to list multiple sets of all of the category codes many times in the same column, and each repeated set of category codes applies to a different individual, then it makes sense to repeat the individual's name on each row beside the category code (like the example in your post #1 where Dave Toni begins in $AP2. To me, this seems like more work because you would have to scroll down the column to the next set of codes and enter another person's name beside that set, and so on. And if the number of category codes changes, you would have to shift the category codes down/up to accommodate additions/deletions to the multiple lists. How you decide to do that is, of course, your choice. Just be sure the formula (or script) references the correct name.

I have doubts that I can develop a VBA solution for this, but others here may be able to offer some assistance. I think the formula solution described in Post #21 serves as an example of what needs to be done conceptually. The challenge involves splitting the J and P columns so that the query can be made...and that might involve up to 20 items separated by + signs in the J and P columns. But before the effort is undertaken, could you say something more about the number of individuals who will be summarized? Would it make more sense to have a single list of category codes in one column, and then place the individuals' names in cells at the top of the worksheet in different, adjacent columns, so that each column represents a summary for whomever is named at the top of the column? If that were done, how many names would need to be accommodated by a worksheet?

 

[K0st4din]

Hello
my idea is the following:
I will adjust the words, I will put my names, I will also choose the dates (because each time the period is different).
As for whether the name cell should be constant, I'm not OK. But it will definitely be easier for me to filter Dave Toni and look at all the specifics of what's going on with him, and then filter my next name.
Here the idea if a macro is made - is for it to perform all the other calculations - starting with what you suggested first - the division and then the scoring according to - date, name, words, as the macro must complete the operation when reaching the last name found in a given worksheet, after which I will go to the next worksheet (I will change the name of the worksheet in the macro itself and continue until the 28th). The other option is for the macro to work under the condition (with one button in each worksheet) in the Active worksheet (things happen again).
P.S - Just to add, no matter how many names are written, the words for each name will be the same - that is, if I have 15 words for Dave, for the next name, they will be again those written by me 15 and so on until the last name in one worksheet.

 

[KRice]

I was suggesting that this type of display might be beneficial...you fill in the date range of interest (green cells) and then type in the names (red cells). The formulas or the VBA code would then return the sums corresponding to the category code words for that date range and person (blue cells).

feb21.xlsx
	AN	AO	AP	AQ	AR
1	4/1/2020	10/30/2020	Dave Toni	Name2	Name3
2		pro	mostri	mostri	mostri
3		MOXI5	0
4		MOXI7	0
5		LEV5	0
6		LEV7	0
7		AMLF10	120
8		AMLF5	9
9		AMOL5/40	0
10		AMV5/160	0
11		BISO5	350
12		CANDF16	0
13		CLOP	0
14		COXI200	0
15		DOXA2	0
16		DOXA4	0
17		IND1.5SR	0
18		IRB150	0
19		IRB300	0
20		LERC10	0
21		LOSF50/12.5	0
22		NEBIF5	0
23		RUS10	330
24		VALCO160/12.5	0
25		VALF160	0
Sheet1

 

[KRice]

Like this...I made one change to the name on row 7:

feb21.xlsx
	B		D						J						P
1	name		date						kkk						point
2	Dave Toni		5/19/2020						RUS10						15
3	Dave Toni		10/20/2020						BISO5						10
4	Dave Toni		10/20/2020						RUS10						15
5	Dave Toni		10/6/2020						BISO5						10
6	Dave Toni		9/21/2020						BISO5						10
7	Mikhail		8/17/2020						BISO5+AMLF10						10+15
8	Dave Toni		5/11/2020						RUS10						15
Sheet1

And without any additional helper columns, and a small change to the formula so that the name refers to the one at the top of the column, the sums for the second name are returned:

feb21.xlsx
	AN	AO	AP	AQ
1	4/1/2020	10/30/2020	Dave Toni	Mikhail
2		pro	mostri	mostri
3		MOXI5	0	0
4		MOXI7	0	0
5		LEV5	0	0
6		LEV7	0	0
7		AMLF10	105	15
8		AMLF5	9	0
9		AMOL5/40	0	0
10		AMV5/160	0	0
11		BISO5	340	10
12		CANDF16	0	0
13		CLOP	0	0
14		COXI200	0	0
15		DOXA2	0	0
16		DOXA4	0	0
17		IND1.5SR	0	0
18		IRB150	0	0
19		IRB300	0	0
20		LERC10	0	0
21		LOSF50/12.5	0	0
22		NEBIF5	0	0
23		RUS10	330	0
24		VALCO160/12.5	0	0
25		VALF160	0	0
Sheet1

Cell Formulas
Range	Formula
AP3:AQ25	AP3	=SUMPRODUCT(($D$2:$D$65>=$AN$1)*($D$2:$D$65<=$AO$1)*($B$2:$B$65=AP$1)*($R$2:$W$65=$AO3)*($X$2:$AC$65))

 

[K0st4din]

And without any additional helper columns, and a small change to the formula so that the name refers to the one at the top of the column, the sums for the second name are returned:

Hello again,
the idea is good, but in the following columns, I have other information that I removed in the example.
As for the last comment, the values shown are not true for AMLF10 (is 120, no 105) and BISO5 (is 350, no 340)
I don't know why they diverge!?
P.S - Aha, the name has been changed so the result changes, it has not been added but has been changed.
So everything is correct!