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)

 

[faresar]

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

That's it! and i was wondering what aaaa represents. Good catch XOR.

Thank you and Gerry for your help.

 

[GerryZ]

@XOR LX
You are magic!!!!!!
When I traslate a formula from Italian to English version I'm Always very carfull of many thigs like commas, dot etc but this case with this very long formula I miss "aaaa" to "yyyy" tahnk you
@Faresar
You are welcome and thank you for your feedbck!!