Need to ignore blank cells

[theta]

Hi

I have the following formula, but if i get a blank in the range it falls over.

How can I ignore blank rows?

=SUMPRODUCT(--(1/COUNTIF(SINGLE!B13:B48,SINGLE!B13:B48)<1),--(1/COUNTIF(SINGLE!B13:B48,SINGLE!B13:B48)))

 

[MrKowz]

What is this formula meant to do? Can you provide some sample data as well as desired outcome?

 

[theta]

It just looks at numbers in column B (in the range B13:B48). If a number appears more than once, it will be counted.

So 1,2,3,4,4,4,5,6,7 would equal a count of 1 (the multiple 4 would produce a 1/COUNTIF of less than one so it will be counted)

 

[theta]

Any ideas at all?

If a blank / zero if present I get a DIV/0 error

Have tried excluding blank zero but doesn't work

 

[theta]

Sample :

1
2
3
4
4
4
5
6
7

Desired result = 1 (counted the duplicate item)


1
2
2
3
4
4
4

Desired result = 2 (counted 2 duplicate items)


1

2
4
4

Desired result = 1 (ignored blank and counted duplicate item)

 

[MrKowz]

Would you be open to a VBA User-Defined-Function alternative? Also, would the data always be sorted in a fashion that the data is grouped like that?

 

[theta]

Can't use any VBA or project..everything locked down tight

It is possible with formulas

I am half way there, just need the countif to ignore blanks as they obv return a DIV/0 error when trying to divide blank by count

 

[theta]

This is a step closer, but I prefer sumproduct solution and also this formula does not distinguish between duplicates and non-duplicates

Just some testing

=SUM(IF(COUNTIF(C13:C30,C13:C30)=0,0,1/COUNTIF(C13:C30,C13:C30)))

 

[Aladin Akyurek]

Hi

I have the following formula, but if i get a blank in the range it falls over.

How can I ignore blank rows?

=SUMPRODUCT(--(1/COUNTIF(SINGLE!B13:B48,SINGLE!B13:B48)<1),--(1/COUNTIF(SINGLE!B13:B48,SINGLE!B13:B48)))

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(SINGLE!$B$13:$B$48<>"",
   MATCH("~"&SINGLE!$B$13:$B$48,SINGLE!$B$13:$B$48&"",0)),
    ROW(SINGLE!$B$13:$B$48)-ROW(SINGLE!$B$13)+1)>1,1))

 

[theta]

Wow...can you explain that in a bit more detail please Al,

Match ~ ?

Thanks!