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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

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
3
Views
618
Replies
20
Views
451
Replies
5
Views
255
Replies
15
Views
194
Replies
3
Views
265

1,141,770
Messages
5,708,436
Members
421,570
Latest member
BaileyJ

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.

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