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)))

 

[theta]

Any way of doing it through SUMPRODUCT, or a conversion of this into sumproduct?

 

[T. Valko]

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)

Try this...

=SUMPRODUCT(--(FREQUENCY(A2:A10,A2:A10)>1))

 

[Aladin Akyurek]

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

Match ~ ?

Thanks!

~ deactivates the special meaning of some chars like < and *. Functions like CountIf or Match are sensitive to their presence. If you think you'll never items surrounded with special meaning chars, you can omit the "~"& and &"" bits from the formula.

 

[Aladin Akyurek]

Any way of doing it through SUMPRODUCT, or a conversion of this into sumproduct?

Why? Formulas with control+shift=enter are essential tools in Excel. Note that the formula
does not count empty cells or formula blanks. Moreover, it works also with non-numeric data.

 

[theta]

Wow thanks guys...learning alot on this thread!

Ah yes you are correct, I may have alpha-numeric references!

One final request

If I wanted to find the nth largest value of column C, based on the above formulas

e.g.

1...5
2...7
3...9
4...11
4...5
4...1
5...7
5...7
5...4
6...1
6...3
7...20

So, if I wanted to find the 1st largest group it would be 5, as the sum of column C is 18

Wanted to find the 1st largest single it would be 7, as its value is 20

Wanted to find the 3rd largest overall, it would be 4 as its value is 17

I am happy with 3 different formula (one for group, one for single, one for overall) but not sure the best way to implement MAX, MIN, LARGE, SMALL with this methods above?

Any help would be great as I can then complete my project tonight!

 

[T. Valko]

Any way of doing it through SUMPRODUCT, or a conversion of this into sumproduct?

Yes, see my other reply.

 

[T. Valko]

I may have alpha-numeric references!



e.g.

1...5
2...7
3...9
4...11
4...5
4...1
5...7
5...7
5...4
6...1
6...3
7...20

Then why are you posting sample data that is all numeric?

I answer the question as it's written/demonstrated/described. If you post all numeric sample data then I make my suggestion based on the sample data being all numeric.

So, if:

I may have alpha-numeric references!

Then disregard my suggestion.

 

[theta]

Very true, apologies

Now for the sumproduct (that will sum numerics in column C based on column B grouping)

Would I wrap Aladin formula as the array element to select valid rows, then sum the corresponding column C, then wrap it all within a LARGE and then wrap the whole thing in a SUMPRODUCT?

 

[theta]

Nearly there I think

{=LARGE(SUM(Formula to select valid rows where FREQUENCY>1, Column C1:C48)),1)}

Just need some help with incorporating Aladin logic into the base of the formula

If this is the correct method?