Counting items using criteria from 3 columns

**gizzard** · 06-09-2011, 11:50 PM

Before I get into the challenge, there's a slight wrinkle—which is that this spreadsheet must exist on EditGrid. That's going to limit creativity, but I can see how it's a better solution an emailing a spreadsheet around.

In the attached spreadsheet, there are 3 columns and to the right of it, there is a small results table. I'd like to count the number of open and future reqs for each department. I used COUNTIFS for a similar Excel spreadsheet but EditGrid doesn't support the formula and these aren't exact matches! The req "numbers" may not always be numbers so I've been trying to figure how I can use COUNTA—I just haven't been able to nail down the logic.

Any tips and advice would be appreciated! Thanks.

**NBVC** · 06-10-2011, 08:38 AM

Sumproduct seems to be supported... try:

=SUMPRODUCT(($A$2:$A$26=$G2)*(C$2:C$26<>""))

copied down and to next column

**MarvinP** · 06-10-2011, 08:42 AM

Hi gizzard.
I have no idea what EditGrid is. Look at the attached that has a Pivot Table to do what I think you want.

**gizzard** · 06-10-2011, 01:49 PM

MarvinP, thanks for the suggestion. EditGrid is an online spreadsheet (like Google Docs) where many people can view and edit simultaneously. It's in no way comparable to Excel as far as functionality, but the sharing aspect is very useful as you can imagine.

NBVC, that formula seems to work - thank you! I would have never thought to use it. Would you mind explaining the logic behind how the formula works?

**NBVC** · 06-10-2011, 02:19 PM

As the function name suggests we are summing the products of two conditions.

Each of the conditions gives an array of TRUEs and FALSEs.

When those arrays are multiplied together you get results of 1's and 0's (1 occurs when TRUE*TRUE and 0 for all other cases)... then those results are summed to give the results.

Here is more on Sumproduct