Counting Non-Duplicate Cells IF

The doomed

Active Member
Joined
Feb 13, 2008
Messages
263
Hi Guys,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I originally posted the other day here > http://www.mrexcel.com/forum/showthread.php?t=369858 however after much head banging I thought it best to start again and reach out for some help, again.<o:p></o:p>
<o:p></o:p>
The Situation Now<o:p></o:p>
I have a sheet containing all my data, 'Closed Projects', and a sheet containing all my stats, 'Stats'.<o:p></o:p>
<o:p></o:p>
I want to look at two columns on 'Closed Projects', Col.BL and Col.BO (All relevant data is contained in Row 4 and beneath if that matters). <o:p></o:p>
<o:p></o:p>
I want to count the number of rows where:<o:p></o:p>
1. Col.BL = 'Stats'!$B$4<o:p></o:p>
2. Col.BO = is unique compare to other values in the column - ie if there is multiple occurrences of the same number I only count one. I don’t want to count blank cells. (The data in Col.BO will be a name or number). I’ve tried using DCOUNTA but couldn’t get it working.<o:p></o:p>
<o:p></o:p>
Note on point 2: the value returned would ideally be 4, using this sample data:<o:p></o:p>
1. 56655
2. 45412
3. 45412
4. <o:p></o:p>

5. 36441<o:p></o:p>
6.
7. 56655<o:p></o:p>

8. 44888
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Try...

Code:
=SUM(IF(FREQUENCY(IF('Closed Projects'!$BL$2:$BL$100='Stats'!$B$4,IF('Closed Projects'!$BO$2:$BO$100<>"",MATCH("~"&'Closed Projects'!$BO$2:$BO$100,'Closed Projects'!$BO$2:$BO$100&"",0))),ROW('Closed Projects'!$BO$2:$BO$100)-ROW('Closed Projects'!$BO$2)+1),1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Upvote 0
Marvellous

:biggrin::biggrin::biggrin:

Many Many Many thanks.


Ive one more question, however I may be better asking on a new thread.

In the formula you provided, the rows are limited to the first 100 rows - thats fine, I can extend that by makeing the 'BL$100', 'BL$200' and so on - right? However, I have several formulas that already have similar problems.

If there anyway I can create a cell that would hold the number of the last row with data in it? That way I could make some of the forumlas relative to the last row of data rather than a random number - which one day will fall over. New rows are added to the sheet weekly, always at the bottom below the current last row.

Any ideas?
 
Last edited:
Upvote 0
Marvellous

:biggrin::biggrin::biggrin:

Many Many Many thanks.

You're very welcome!


Ive one more question, however I may be better asking on a new thread.

In the formula you provided, the rows are limited to the first 100 rows - thats fine, I can extend that by makeing the 'BL$100', 'BL$200' and so on - right? However, I have several formulas that already have similar problems.

If there anyway I can create a cell that would hold the number of the last row with data in it? That way I could make some of the forumlas relative to the last row of data rather than a random number - which one day will fall over. New rows are added to the sheet weekly, always at the bottom below the current last row.

Any ideas?

Which version of Excel are you using?
 
Upvote 0
Convert your data into a list...

Data > List > Create List

The range will automatically adjust as data is added/removed.
 
Upvote 0
Getting problem due to autofilters on sheet - however I need them.

If there's something else straight forward then great, if not, I'm happy enough to leave it.
 
Upvote 0
The formula
=SUMPRODUCT(--(BL4:BL1000=Stats!B4),1/COUNTIF(BO4:BO1000,BO4:BO1000&""))
will return the number that you want
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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