Search code examples
excelcountifnamed-ranges

Excel > Exclude a named range from COUNTIFS

I've got a spreadsheet with hundred of rows, each with a unique ID and each designated to a person. On a different tab is a summary sheet where my formula goes (count of IDs for each person). On a third sheet, I have a named range 'excludes' which has certain IDs included in a single column.

i.e my sheet1 has:

ID  Name
1   Bob
2   Bob
3   Bob
4   Shirley
5   Ted
6   Michael

The named range has say IDs, 2 and 3 in it, so I want my COUNTIFS to return the value 1 for Bob.

ID
2
3

Here's my formula but I think I'm missing something: =COUNTIFS('Sheet1'!A:A,'summary'!$A1,'Sheet1!A:A,"<>excludes")

I want to exclude every ID in the excludes named range from my COUNTIFS result. Do I need to use sumproduct or something?

Thanks for your help!

Solution

  • Use SUMPRODUCT:

    =SUMPRODUCT((B2:B7=D2)*(ISERROR(MATCH(A2:A7,excludes,0))))

    enter image description here

    If you want countifs then you will need to count the whole and subtract where it exists in the named range:

    =COUNTIFS(B:B,D2)-SUMPRODUCT(COUNTIFS(B:B,D2,A:A,excludes))

    enter image description here