Distinct Count excluding Blank

anelem

New Member
Joined
Jul 18, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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.

1621675850681.png
 

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
Joined
Dec 30, 2008
Messages
13,599
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

navic

Active Member
Joined
Jun 14, 2015
Messages
333
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Jul 18, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
This is perfect!
Thank you again - you guys are geniuses!!
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top