COUNTIF/SUMPRODUCT range problems

Hi All,

I have text (staff names) in ranges E92:E123 & E131:E133. At times not all of the cells in these ranges are populated with names. I need to count all the cells that have entries, duplicates only once, and leave the formula cell empty when the ranges are not populated. However, I am having great trouble combining the ranges into the COUNTIF formula.

This works but does not combine both ranges:

Please Login or Register  to view this content.

I have tried:

Please Login or Register  to view this content.

which results in too few arguments. I have tried many other combinations for combining the ranges in the formula but am left scratching my head.

How do I combine the ranges?

Many thanks,

Suggest you post a sample workbook.

Regards, TMS

hard to say without seeing a sample, but perhaps...

	A	B
1	aa	6
2	bb
3	cc
4	aa
5	cc
6	dd
7	ee
8	aa
9	title1
10	title2
11	aa
12	ff

=SUMPRODUCT(1/COUNTIF(A1:A12,A1:A12))-2

I've attached a sample cut from the worksheet. I hope this explains better.

=SUMPRODUCT((E92:E123<>"")/(COUNTIF(E92:E123,E92:E123&"")) works but does not include the range E121:E134.

I don't know how to include the second range in the formula to be counted as one range.

Thanks,

Try this...
=SUMPRODUCT((E92:E133<>"")/COUNTIF(E92:E133,E92:E133&""))-COUNTA(E124:E130)

Thanks very much FDibbins & Aladin. Aladin, your formula did the trick! Is there a way for cell A10 to be empty "" when the result is 0. I tried using your formula in an IF function but received an error.

p.s. what form of function uses braces?

Many thanks,

Try this...
=IF(COUNTA(E92:E133)=0,"",SUMPRODUCT((E92:E133<>"")/COUNTIF(E92:E133,E92:E133&""))-COUNTA(E124:E130))

{} are generally used to denote ARRAY formulas. This is a copy from the link below. If you google excel array, you will find many more...

An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and use the array formula to calculate a column or row of subtotals. You can also place an array formula in a single cell and then calculate a single amount. An array formula that resides in multiple cells is called a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula.

https://support.office.com/en-ca/art...2-ecfd5caa57c7

Thanks FDibbins, thats perfect and a little easier for me to work out also. Thanks also for the snippet on array formulas. Very helpful!

Regards,

Happy to help, and thanks for the feedback

Hi Aladin,

Thank you for your second solution. It does the job and as your link proves, is faster. I agree that 0 does sound more natural but it did not fit with the format of the worksheet in this application. I look forward to understanding array formulas!

Many thanks,