Need help with COUNTIFS

**rival2031** · 03-15-2013, 07:04 PM

Hello Everyone,

Stuck on my use of the countifs formula. I want A15 and A18 to count open and closed claims given the criteria input in A13. It actually works in counting open and closed claims total, but I do not want certain claim types counted -- specifically "Disability", "Pending", and "Duplicate" listed in column B)

For example, if 1/1/2001 is selected, it should read Open and Re-Opened Claims: 1 Closed Claims: 1 (Not Open and Re-opened:3 and Closed: 1)

I've attached below... thanks in advance...

**Harribone** · 03-15-2013, 07:54 PM

In your spreadsheet you do have 3 Open and Re-Opened Claims for 01/01/2001? Do you Want this to just return 1 not matter how many are found?

**JBeaucaire** · 03-15-2013, 07:55 PM

1) move F1:F5 down to F2:F6
2) put this formula in G2, then copy down/across the table:

=COUNTIFS($A$1:$A$10, G$1, $C$1:$C$10, $F2, $B$1:$B$10, "<>Duplicate*", $B$1:$B$10, "<>Pending", $B$1:$B$10, "<>Disability")

3) Apply in other ways as you wish