Count number of lines based on text and number criteria, without duplicates

[HVCompleto]

Hello! New user here!
I've been trying to figure out for a day and can't seem to find any help on any thread for this, so I decided to post a new one.
Below is the sample database for my problem. The final goal is to have a cell that returns the number of times a unique brand (Column A with no duplicates) is in Store (column B = YES) and with values below 0 (Column C <0).
So in this case, the cell should return 3!



[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl64, width: 64"]Brand[/TD]
[TD="class: xl64, width: 64"]In store?[/TD]
[TD="class: xl64, width: 64"] Value[/TD]
[/TR]
[TR]
[TD]3m[/TD]
[TD]yes[/TD]
[TD="align: right"]-300[/TD]
[/TR]
[TR]
[TD]3m[/TD]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3m[/TD]
[TD]yes[/TD]
[TD="align: right"]-233[/TD]
[/TR]
[TR]
[TD]3m[/TD]
[TD]no[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]aeg[/TD]
[TD]yes[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]aeg[/TD]
[TD]no[/TD]
[TD="align: right"]-200[/TD]
[/TR]
[TR]
[TD]aeg[/TD]
[TD]yes[/TD]
[TD="align: right"]-300[/TD]
[/TR]
[TR]
[TD]aeg[/TD]
[TD]no[/TD]
[TD="align: right"]-200[/TD]
[/TR]
[TR]
[TD]bosch[/TD]
[TD]yes[/TD]
[TD="align: right"]-100[/TD]
[/TR]
[TR]
[TD]bosch[/TD]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]bosch[/TD]
[TD]yes[/TD]
[TD="align: right"]-50[/TD]
[/TR]
[TR]
[TD]bosch[/TD]
[TD]no[/TD]
[TD="align: right"]-500[/TD]
[/TR]
[TR]
[TD]osaka[/TD]
[TD]yes[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]osaka[/TD]
[TD]no[/TD]
[TD="align: right"]-20[/TD]
[/TR]
[TR]
[TD]osaka[/TD]
[TD]yes[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]osaka[/TD]
[TD]no[/TD]
[TD="align: right"]-100[/TD]
[/TR]
</tbody>[/TABLE]

 

[Weazel]

maybe something like...

Excel 2016 (Windows) 32 bit[TABLE="class: grid, width: 300"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Brand[/TD]
[TD]In store?[/TD]
[TD]Value[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]3m[/TD]
[TD]yes[/TD]
[TD="align: right"]-300[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]3m[/TD]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]3m[/TD]
[TD]yes[/TD]
[TD="align: right"]-233[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]3m[/TD]
[TD]no[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]aeg[/TD]
[TD]yes[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]aeg[/TD]
[TD]no[/TD]
[TD="align: right"]-200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]aeg[/TD]
[TD]yes[/TD]
[TD="align: right"]-300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]aeg[/TD]
[TD]no[/TD]
[TD="align: right"]-200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]bosch[/TD]
[TD]yes[/TD]
[TD="align: right"]-100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]bosch[/TD]
[TD]no[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]bosch[/TD]
[TD]yes[/TD]
[TD="align: right"]-50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]bosch[/TD]
[TD]no[/TD]
[TD="align: right"]-500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]osaka[/TD]
[TD]yes[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]osaka[/TD]
[TD]no[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]osaka[/TD]
[TD]yes[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]osaka[/TD]
[TD]no[/TD]
[TD="align: right"]-100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF(A2:A17<>"",IF(B2:B17="yes",IF(C2:C17<0,MATCH(A2:A17,A2:A17,0)))),ROW(A2:A17)-ROW(A2)+1),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[HVCompleto]

Thank you!!

I need to learn how to work with array formulas, they're so helpful!!
This works perfectly!