Using array formulas to count and sum

If none of the standard counting techniques fits the bill, you may be able to construct an array formula (see "Array Formulas" earlier in this chapter). Don't forget: When you enter an array formula, press Ctrl+Shift+Enter.

To count the number of numerical values (skipping text and blanks), use this array formula:

To count the number of cells that contain an error value, use this array formula:

To count the number of unique numeric values (skipping text, blanks not allowed), use this array formula:

Table 3-4 shows a number of array formula examples based on the worksheet shown in Figure 3-7.

Figure 3-7: This simple database demonstrates some useful array formulas for counting and summing.

This workbook (including the formulas shown in Table 3-4) is available on the companion CD-ROM.

Table 3-4 COMPLEX ARRAY FORMULAS WITH THE SUM FUNCTION

Array Formula

=SUM((A2:A10="Jan")*(B2:B10="North") *C2:C10)

=SUM((A2:A10="Jan")*(B2:B10<>"North") *C2:C10)

=SUM((A2:A10="Jan")*(B2:B10="North"))

=SUM((A2:A10="Jan")*((B2:B10="North")+ (B2:B10="South")))

Returns

Sum of Sales where Month="Jan" AND Region="North"

Sum of Sales where Month="Jan" AND Region<>"North"

Count of Sales where Month="Jan" AND Region="North"

Count of Sales where Region="North" or "South" and Month="Jan"

Continued

 Array Formula Returns =SUM((A2:A10= 'Jan")*(C2:C10>=200) Sum of Sales where Month="Jan" and *(C2:C10)) Sales>=200 =SUM((C2:C10>= =300)*(C2:C10<=400) Sum of Sales between 300 and 400 *(C2:C10)) =SUM((C2:C10> =300)*(C2:C10<=400)) Count of Sales between 300 and 400
0 0