Is there a worksheet function that will allow me to determine if all entries in a range of numbers are unique, and if not, which numbers are duplicates?
	How can I create a worksheet function to sum all the values in a column of numbers that are between two rows?
	I need a MIN function that will return the minimum non-zero value. My data range contains many zeroes and the Excel MIN function always returns zero.
	I need an AVERAGE function that will return the average of all non-zero values. My data range contains many zeroes and the Excel AVERAGE function always includes these.
	I need to sum all the values in column C only where the value in column A equals "Firm" AND the value in column B equals "Technician". How do I create a worksheet function to do this?

Is there a worksheet function that will allow me to determine if all entries in a range of numbers are unique, and if not, which numbers are duplicates?
Keywords: Array Formulas, Duplicates, Unique Values
Posted November 26, 1996

To accomplish this, you need two array formulas. The first formulas is array-entered in a single cell. It will return True if all items in your range are unique or False if there are duplicates. I will assume that range A1:A10 is the range you are interested in.

=MAX(COUNTIF(A1:A10, A1:A10))=1

The second formulas must be array-entered in a separate column beside each of the items in your list. It will return 1 beside any entry that is unique, and an integer indicating the number of duplicates beside any entry that is not unique.

=COUNTIF(A1:A10, A1:A10)

Remember, array formulas are entered by pressing CTRL-SHIFT-ENTER instead of just the ENTER key alone.

How can I create a worksheet function to sum all the values in a column of numbers that are between two rows?
Keywords: Array Formulas, Sum
Posted November 26, 1996

I'll assume that your data is in column A, rows 1 through 10, and that you want to sum the values from rows 4 to 8, inclusive. You can do this with the following array formula:

=SUM(IF(ROW(A1:A10)>=4,IF(ROW(A1:A10)<=8,A1:A10,0),0)) 

You can replace the hard-coded 4 and 8 with cell references to make the formula more flexible. Remember, array formulas are entered by pressing CTRL-SHIFT-ENTER instead of just the ENTER key alone.

I need a MIN function that will return the minimum non-zero value. My data range contains many zeroes and the Excel MIN function always returns zero.
Keywords: Array Formulas, Min
Posted November 26, 1996

I'll assume your data is in range A1:A10. Here is an array formula MIN that will ignore zero values:

=MIN(IF(A1:A10>0,A1:A10,""))

Remember, array formulas are entered by pressing CTRL-SHIFT-ENTER instead of just the ENTER key alone.

I need an AVERAGE function that will return the average of all non-zero values. My data range contains many zeroes and the Excel AVERAGE function always includes these.
Keywords: Array Formulas, Average
Posted November 26, 1996

I'll assume your data is in range A1:A10. Here is an array formula AVERAGE that will ignore zero values:

=AVERAGE(IF(A1:A10=0,"",A1:A10))

Remember, array formulas are entered by pressing CTRL-SHIFT-ENTER instead of just the ENTER key alone.

I need to sum all the values in column C only where the value in column A equals "Firm" AND the value in column B equals "Technician". How do I create a worksheet function to do this?
Keywords: Array Formulas, Sum
Posted November 26, 1996

I'll assume your data is in range A1:C10. Here is an array formula that will sum only the values in column C that meet your two criteria:

=SUM((A1:A10="Firm")*(B1:B10="Technician")*C1:C10)

Remember, array formulas are entered by pressing CTRL-SHIFT-ENTER instead of just the ENTER key alone.

Back to Top

Back to the FAQ Table of Contents

There are 3 kinds of people: those who can count & those who can't.