Unique Count when Values > 0.01

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a spreadsheet with the following cols :-

ColA ColB ColC

Vendor1 0 5.99
Vendor2 1.99 0
Vendor1 2.00 3.99
Vendor1 0 1.00

Total 3.99 10.98
# of Vendors 2 1

The # of Vendors needs to be counted when the value in the particular column
is greater than 0.01, but only for each unique vendor.

Is this possible ?

Thanks
Jill
 
Jill1 said:
Hi

I have a spreadsheet with the following cols :-

ColA ColB ColC

Vendor1 0 5.99
Vendor2 1.99 0
Vendor1 2.00 3.99
Vendor1 0 1.00

Total 3.99 10.98
# of Vendors 2 1

The # of Vendors needs to be counted when the value in the particular column
is greater than 0.01, but only for each unique vendor.

Is this possible ?

Thanks
Jill
Click to expand...


Hi Jill,

Maybe it is overkill but i think you can use something like this:

=SUM(IF(FREQUENCY(IF(($A$1:$A$4<>"")*($B$1:$B$4>0.01),MATCH($A$1:$A$4,$A$1:$A$4,0)),ROW(INDIRECT("1:"&ROWS($A$1:$A$4))))>0,1,0))

Where Vendors are in A and nmbers in B
It is an array formula so you have to confirm it with Ctrl + Shift +
Enter

Regards,
Bondi
 
Thanks that worked peefectly !!

Bondi said:
Hi Jill,

Maybe it is overkill but i think you can use something like this:

=SUM(IF(FREQUENCY(IF(($A$1:$A$4<>"")*($B$1:$B$4>0.01),MATCH($A$1:$A$4,$A$1:$A$4,0)),ROW(INDIRECT("1:"&ROWS($A$1:$A$4))))>0,1,0))

Where Vendors are in A and nmbers in B
It is an array formula so you have to confirm it with Ctrl + Shift +
Enter

Regards,
Bondi
Click to expand...
 
Back
Top