=COUNTIFS to add additional cells

[ExcelFan23]

Hello,

See attached.

I am trying to update the =COUNTIFS formula in C17 to + the total staff in C1:C10 excluding C5

Such a simple thing but nothing I try works.

Any help would be greatly appreciated...

Thanks

[63falcondude]

Try this:
=COUNTA(C1:C10)-1

[ExcelFan23]

Thanks but need to keep the COUNTIFS formula. See the attachment.

[Sam Capricci]

this would be one way... =COUNTIFS(C18:C32, "*",C18:C32,"<>*Team*")+COUNTIFS(C1:C10, "*",C1:C10,"<>*apollo*")

[63falcondude]

Thanks but need to keep the COUNTIFS formula. See the attachment.

Okay, then this:

=COUNTIFS(C18:C32, "*",C18:C32,"<>*Team*")+COUNTA(C1:C10)-1

[ExcelFan23]

this would be one way... =COUNTIFS(C18:C32, "*",C18:C32,"<>*Team*")+COUNTIFS(C1:C10, "*",C1:C10,"<>*apollo*")

Thanks Sambo!

[ExcelFan23]

Okay, then this:

=COUNTIFS(C18:C32, "*",C18:C32,"<>*Team*")+COUNTA(C1:C10)-1

Thanks Falcon!

[Sam Capricci]

now, this is a slightly different take on it BUT will only work if you DO NOT put it in C17 as that will create a circular reference...
=SUM(COUNTIFS(C1:C32,"<>"&"*team*",C1:C32,"<>"&"*apollo*",C1:C32,"<>"))

Oh, and thank you for the rep!

EDIT: in fact you could put this in B17 which would take care of that issue...
="Number of staff ** = "&SUM(COUNTIFS(C1:C32,"<>"&"*team*",C1:C32,"<>"&"*apollo*",C1:C32,"<>"))