Excel > Exclude a named range from COUNTIFS

0
votes

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!

1
excelcountifnamed-ranges
To be clear, you'd want 1, but also 4, 5, 6 correct? – BruceWayne

1 Answers

2
votes

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