Count all but a specific reference cell (countif ?) +

[Steve]

I'm using count if for the master counts of a fantasy football team:
QBs 2 <- =countif($a$3:$a$20,"QB") etc
RBs 5
WR/Tes 6
K 1
Def 2
Total 16

Below shows the columns indicating the bye weeks for that particular player.
Cat Bye

Def 4
QB 5
RB 6
WR 6
QB 6
K 6
WR 6
RB 7
RB 7
RB 8
WR 8
TE 8
WR 8
Def 9
WR 10
RB 10

How can I get the week counts e.g. in week 6 that would show the following
(indicating that in week 6, I'll only have these players available):
e.g. If 6 was entered in a specific cell(F1), then
QBs 1
RBs 4
WR/Tes 4
K 0
Def 2
Total 11
or if 8 was entered in that specific cell(F1), then
QBs 2
RBs 4
WR/Tes 3
K 1
Def 2
Total 12

etc

Thanks,

 

[Max]

One way for multi-criteria counting is to use sumproduct ..

Assume source data for Cat, Bye is within A3:B20

In F1 will be the week# (Bye col):
In E2 down will be listed the cats eg; QB, RB, WR, TE, etc

Then in F2: =SUMPRODUCT((B$3:B$20=F$1)*(A$3:A$20=E2))
with F2 copied down, will return the required "week counts" for each cat

For combined cats, eg: WR/TEs, you could easily do the additional roll-ups
(sums) in another range, either below/adjacent to col F

 

[Steve]

Thanks,

Worked great.

One way for multi-criteria counting is to use sumproduct ..

Assume source data for Cat, Bye is within A3:B20

In F1 will be the week# (Bye col):
In E2 down will be listed the cats eg; QB, RB, WR, TE, etc

Then in F2: =SUMPRODUCT((B$3:B$20=F$1)*(A$3:A$20=E2))
with F2 copied down, will return the required "week counts" for each cat

For combined cats, eg: WR/TEs, you could easily do the additional roll-ups
(sums) in another range, either below/adjacent to col F

 

[Max]

welcome, Steve.