Software

Use SUM in an array function to count values that match multiple criteria

When you need to count values based on more than one criterion, the CountIF function won't do the job. The solution is to build an array formula with SUM.

If you want to count the number of days that orders totaled 30,000 or more, you can use the CountIF function. But what if you want to know the number of days that orders totaled more than 30,000 but less than 50,000? To count items that meet two or more criteria, you can use an array formula with the SUM function.

First, use conditional formatting to highlight the cells we want to count.

  1. Go to Format | Conditional Formatting. In Excel 2007, on the Home tab, click Conditional Formatting.
  2. Select Formula IS. In Excel 2007, click New Rule and then click Use A Formula To Determine Which Cells To Format.
  3. Enter the following formula: =AND($D2>=30000,$D2<50000)
  4. Click the Format button.
  5. Click yellow on the Patterns. In Excel 2007, on the Fill tab, select yellow under Background Color (Figure A) and click OK.
  6. Click OK again.

Figure A

To calculate the number of days sales totaled more than 30,000 but less than 50,000, follow these steps:

  1. Click in G2.
  2. Enter the following formula: =SUM((D2:D15>=30000)*(D2:D15<50000))
  3. Press [Ctrl] + [Shift] + [Enter] (Figure B).

Figure B

To calculate the total sales for those two days, follow these steps:

  1. Click in L2.
  2. Enter the following formula: =(SUM(IF(D2:D15>=30000,IF(D2:D15<50000,D2:D11))))
  3. Press [Ctrl] + [Shift] + [Enter] (Figure C).

Figure C


Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks