SUM data by extracting number from complex cell after meeting some conditions.

[faresar]

[TABLE="class: grid"]
<tbody>[TR]
[TD]user[/TD]
[TD]Status[/TD]
[TD]Date[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]No[/TD]
[TD]12/12/2014[/TD]
[TD][TABLE="width: 408"]
<tbody>[TR="class: grid"]
[TD="align: left"]no;;;;;;;;[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]Yes[/TD]
[TD]02/05/2015[/TD]
[TD][TABLE="width: 408"]
<tbody>[TR="class: grid"]
[TD="align: left"]yes; 112.00; 20.00; 3.00; 4.00; 16.00; 2014-08-13; 1792.00;.75[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD]Yes[/TD]
[TD]03/03/2015[/TD]
[TD][TABLE="width: 408"]
<tbody>[TR="class: grid"]
[TD="align: left"]no;;;;;;;;[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]Yes[/TD]
[TD]06/08/2014[/TD]
[TD][TABLE="width: 408"]
<tbody>[TR="class: grid"]
[TD="align: left"]no;;;;;;;;[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]No[/TD]
[TD]07/11/2015[/TD]
[TD][TABLE="width: 408"]
<tbody>[TR="class: grid"]
[TD="align: left"]yes; .00; .00; .00; .00; .00; 2014-09-19; .00;.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]Yes[/TD]
[TD]02/02/2015[/TD]
[TD][TABLE="width: 408"]
<tbody>[TR="class: grid"]
[TD="align: left"]yes; 20.00; 300.00; 15.00; 15.00; 285.00; 2013-10-31; 5700.00;2.37[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD]No[/TD]
[TD]05/09/2014[/TD]
[TD][TABLE="width: 408"]
<tbody>[TR="class: grid"]
[TD="align: left"]yes; 5.00; .00; .00; .00; .00; ; .00;.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]Yes[/TD]
[TD]09/09/2015[/TD]
[TD][TABLE="width: 408"]
<tbody>[TR="class: grid"]
[TD="align: left"]yes; 1.00; 40.00; 10.00; 5.00; 35.00; 2014-03-18; 35.00;.01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]Yes[/TD]
[TD]06/12/2015[/TD]
[TD][TABLE="width: 408"]
<tbody>[TR="class: grid"]
[TD="align: left"]yes; 10.00; 10.00; 10.00; 10.00; .00; 2015-02-02; .00;.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hello Excel gurus,

I am working on a dashboard that automatically counts and sums data from thousands of rows. The above table is a short example of source data.I have a quite challenging and interesting excel problem that i am unable to solve due to my limited knowledge in function. Therefore i need to ask you for help.

I need to SUM the number that are in bold (4th column) but after meeting certain conditions.

Conditions
So first condition is User column = Name 2 (or another cell referring to "Name 2")
2nd condition is that status should be "Yes"
3rd condition is to include only 2015 data, referring to 3rd column
4th condition is if 'data' column starts with yes, then we should SUM the numbers that are in bold (Red)

The 4th one is the hardest to do. I think i need SUMIFS or SUMPRODUCT with combination of other functions

Expected outcome is 7527

Some queues:
- There are always 8 semicolons. The number is always after 7th semicolon from left to right, or after 2nd semicolon from right to left.
- As seen in example above, some numbers can be 3 or 2 or 4 or no digit before .00
- The function will be checking exactly 40,000 rows so i need only one function in a cell to do the job

(i dont know why the 4th column cells are surrounded by extra border. Sorry for that)

 

[GerryZ]

hmmmm the forth condition i guess is impossible because doesn't make different the format of the charcter
question is the forurth condition Always after the Sixth semicolom?

 

[GerryZ]

Book1
	A	B	C	D
1	user	Status	Date	Data
2	Name 1	No	12/12/14	no;;;;;;;;
3	Name 2	Yes	2/5/15	yes; 112.00; 20.00; 3.00; 4.00; 16.00; 2014-08-13; 1792.00;.75
4	Name 3	Yes	3/3/15	no;;;;;;;;
5	Name 2	Yes	6/8/14	no;;;;;;;;
6	Name 2	No	7/11/15	yes; .00; .00; .00; .00; .00; 2014-09-19; .00;.00
7	Name 2	Yes	2/2/15	yes; 20.00; 300.00; 15.00; 15.00; 285.00; 2013-10-31; 5700.00;2.37
8	Name 4	No	5/9/14	yes; 5.00; .00; .00; .00; .00; ; .00;.00
9	Name 2	Yes	9/9/15	yes; 1.00; 40.00; 10.00; 5.00; 35.00; 2014-03-18; 35.00;.01
10	Name 2	Yes	6/12/15	yes; 10.00; 10.00; 10.00; 10.00; .00; 2015-02-02; .00;.00
11
12	1 criteria	2nd criteria	3rd criteria	4th criteria bold number
13	name 2	yes	2015
14
15	RESULT
16	7527
Foglio4

Cell Formulas
Range	Formula
A16	{=SUMPRODUCT(IFERROR(LEFT(TRIM(RIGHT(SUBSTITUTE($D$2:$D$10,";",REPT(" ",50)),100)),FIND(".",TRIM(RIGHT(SUBSTITUTE(D2:D10,";",REPT(" ",50)),100)))-1)+0,""),--($A$2:$A$10=$A$13),--($B$2:$B$10=$B$13),--(TEXT($C$2:$C$10,"aaaa")+0=$C$13))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[faresar]

Hello GerryZ, thanks but i tried the command with Ctrl+Shift+Enter and i get #VALUE

it worked for you?

I am using Excel 2013 with no toolpak.

 

[GerryZ]

Hello works fine For me with Excel 2013 italian Version, should work For you as well
Whe I Will be home I Will post a dropbox link so you can test it

 

[GerryZ]

Hello GerryZ, thanks but i tried the command with Ctrl+Shift+Enter and i get #VALUE

it worked for you?

I am using Excel 2013 with no toolpak.

Hello as you can see it works in my Excel 2013 italian version
make sure on your data the commas(,) semicolumn( and period (.)
let me know!

https://www.dropbox.com/s/pstw62d85... cell after meeting some conditions.xlsx?dl=0

 

[faresar]

I cant seem to find the problem. I downloaded your file but i see the same #VALUE
i use commas.

 

[GerryZ]

Hello To activate this formula you must do CTRL+SHIF+ENTER and not just Enter!
because this is an array formula
let me know!

 

[faresar]

i know. i did but still i get #VALUE
My excel is pretty standard. No modification in the settings so i am not sure why we have differences. I will check if my friend is getting the same.

 

[XOR LX]

The issue is the part:

TEXT($C$2:$C$10,"aaaa")

"a" represents "anno" in Italian, which needs to be "y" (for "year") in English-language versions, i.e.:

TEXT($C$2:$C$10,"yyyy")

Regards