# Distinct Count excluding Blank

#### anelem

##### New Member
I have an Excel worksheet that is downloaded from a server (Sheet2).
The number of rows vary each time the report is generated.
I have a summary worksheet (Sheet1) with formulae results. Not everyone uses 365 so am looking for a formulae to calculate the following on the uploaded worksheet :
1. Count the number of distinct values in Column A if Column B value is positive (>0)
2. Count the number of distinct values in Column A if Column B value is negative (<0) ((0 values are cleared by a macro))
3. Then subtract value in 2. from 1.
4. Because the number of rows vary need to be able to include additional rows in the formula so that should data populate those will also be counted.
The formulae should incorporate blank cells in rows 15 onwards.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### jasonb75

##### Well-known Member
The formulae should incorporate blank cells in rows 15 onwards.
Or you could use dynamic named ranges.

Note that this suggestion assumes that all job numbers are proper numbers as per the screen capture.

For some reason XL2BB is not picking up the named range definitions on this mini sheet.

Last_row refers to:- =MATCH(1E+100,Sheet1!\$B:\$B)
Job
refers to:- =Sheet1!\$A\$2:INDEX(Sheet1!\$A:\$A,Last_row)
Total
refers to:- =Sheet1!\$B\$2:INDEX(Sheet1!\$B:\$B,Last_row)

Book1
ABCDE
1JobTotal
265150953.21Positive9
3651501110.92Negative2
465194892.64Difference7
5652001005
665216853.75
765290734.67
865295859.04
9652951005
1065299-1830.04
1165353-1588.13
1265355745.91
13654151130
14654241011.3
Sheet1
Cell Formulas
RangeFormula
E2E2=SUM(--(FREQUENCY(IF(Total>0,Job),Job)>0))
E3E3=SUM(--(FREQUENCY(IF(Total<0,Job),Job)>0))
E4E4=E2-E3

#### navic

##### Active Member
Try ARRAY formulas below (adjust range)
Code:
``=SUM(IF(FREQUENCY(IF(\$B\$2:\$B\$100<0,\$A\$2:\$A\$100),\$A\$2:\$A\$100)>0,1))``
Code:
``=SUM(IF(FREQUENCY(IF(\$B\$2:\$B\$100>0,\$A\$2:\$A\$100),\$A\$2:\$A\$100)>0,1))``

#### anelem

##### New Member
This is perfect!
Thank you again - you guys are geniuses!!

Replies
5
Views
168
Replies
6
Views
139
Replies
5
Views
208
Replies
3
Views
190
Replies
3
Views
455

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,127
Messages
5,768,286
Members
425,460
Latest member
Astros1243

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back