Using array formula to total entries

**RichardL** · 01-20-2008, 02:18 AM

You guys helped me the other day immensely, but I'm stuck again.

See the attached spreadsheet:

I'm trying to total, on the Avgs2-14 worksheet, the number of Health quotes of 2 to 14 lives from the Other worksheet. The criteria I'm using is number of lives (column h on the Other worksheet) and product (column f on the Other worksheet). I want to have a total on the Avgs2-14 page of quotes run for health with lives between 2 and 14. This formula is in C5 on the Avgs2-14 worksheet. I know how to do a DCOUNT, but I think an array formula would be much easier.

Someone please take a look at the formula I'm using in cell C5 on the Avgs2-14 worksheet and tell me what I'm doing wrong. This is driving me insane. Thanks.

**Max, Singapore** · 01-20-2008, 02:39 AM

In Avgs2-14,
Think you could try this instead in C5:
=SUMPRODUCT((Other!H2:H128>=2)*(Other!H2:H128<=14)*(Other!F2:F128=$B$3))

**RichardL** · 01-20-2008, 02:43 AM

Yep, it worked. Not sure why, but that's just one more thing to learn. Thanks!

**Max, Singapore** · 01-20-2008, 02:58 AM

Originally Posted by RichardL

Yep, it worked. Not sure why, but that's just one more thing to learn. Thanks!

Welcome.

The multiplication of the 3 criteria ranges:
(Other!H2:H128>=2)*(Other!H2:H128<=14)*(Other!F2:F128=$B$3)
produces a resulting array of 1's/0's depending on whether the criteria is satisfied (1's) or not (0's), ie something like this: {0;0;1;0;1 ...}.
Sumproduct then adds up all the 1's to give the final resulting count.