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 calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
Solution
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))
 
Upvote 0
This is perfect!
Thank you again - you guys are geniuses!!
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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
Back
Top