Excel 2007 : Simplify my COUNTIFS formula

**AlaaAlii** · 04-09-2012, 07:23 AM

Hello. This is the formula I'm using:

Please Login or Register  to view this content.

It's a bunch of COUNTIFS (it goes up to A107).
This counts the entries in column L that contain WORKINPROGRESS, and entries in column F that contain the contents of cell B2 (which is a date), and then the entries in column I that contains "NameA", "NameB", "NameC",...

Example:
Count everything that has column L = WORKINPROGRESS, column F = 8-Apr-2012, column I = David AND
Count everything that has column L = WORKINPROGRESS, column F = 8-Apr-2012, column I = John AND.... and so one.

My formula is just a COUNTIFS for each of my above "criteria". The list of names are in cells A84 to A107. My issue is that this list of names is growing, so each time a name is added, I have to edit the formula to add another COUNTIFS at the end. And it's not just one formula, there are about 20 other formulas that, for example, count column L = CLOSED instead.

So is there a way to simplify this?

**daddylonglegs** · 04-09-2012, 07:29 AM

Try like this

=SUMPRODUCT(COUNTIFS(L2:L20000,"WORKINPROGRESS",F2:F20000,B2,I2:I20000,A84:A107))

You can even make A84:A107 larger to account for future expansion, that should affect the results

**AlaaAlii** · 04-09-2012, 07:35 AM

Thank you, it worked, and this is exactly what I needed! I stumbled upon the SUMPRODUCT function in my search before, but I don't think I nested COUNTIFS in there.