Multiple COUNTIF

[Francis Hookham]

70 rows with 4 groups of columns
D S:AE AH:AT and AW:BI

PROBLEM 1
I need to COUNTA the letters which are in them
but I want to discount "X" when they occurs

This works in BN1

=COUNTA(D11)-COUNTIF(D11,"x")+COUNTA(S1:AE1)-COUNTIF(S1:AE1,"x")+COUNTA(
AH1:AT1)-COUNTIF(AH1:AT1,"x")+COUNTA(AW1:BI1)-COUNTIF(AW1:BI1,"x")

and the COUNTAs can be combine can to this

=COUNTA(D11,S1:AE1,AH1:AT1,AW1:BI1)-COUNTIF(D11,"x")-COUNTIF(S1:AE1,"x")
-COUNTIF(AH1:AT1,"x")-COUNTIF(AW1:BI1,"x")

but the COUNTIFs don't like being combined so this does not work

=COUNTA(D11,S1:AE1,AH1:AT1,AW1:BI1)-COUNTIF((D11,S1:AE1,AH1:AT1,AW1:BI1)
,"x")

PROBLEM 2
is similar but I need the same COUNTA(D11,S1:AE1,AH1:AT1,AW1:BI1)
but need this time to discount "-" and "ill" so will need two COUNTIFs so I
need multiple ranges AND multiple COUNTIF

I shall manage them the l o n g way but it is always nice to refine things
and there is always a better way of doing it

Many thanks - again

Francis Hookham

 

[J.E. McGimpsey]

70 rows with 4 groups of columns
D S:AE AH:AT and AW:BI

PROBLEM 1
I need to COUNTA the letters which are in them
but I want to discount "X" when they occurs

This works in BN1

=COUNTA(D11)-COUNTIF(D11,"x")+COUNTA(S1:AE1)-COUNTIF(S1:AE1,"x")+COUNTA(
AH1:AT1)-COUNTIF(AH1:AT1,"x")+COUNTA(AW1:BI1)-COUNTIF(AW1:BI1,"x")

and the COUNTAs can be combine can to this

=COUNTA(D11,S1:AE1,AH1:AT1,AW1:BI1)-COUNTIF(D11,"x")-COUNTIF(S1:AE1,"x")
-COUNTIF(AH1:AT1,"x")-COUNTIF(AW1:BI1,"x")

but the COUNTIFs don't like being combined so this does not work

=COUNTA(D11,S1:AE1,AH1:AT1,AW1:BI1)-COUNTIF((D11,S1:AE1,AH1:AT1,AW1:BI1)
,"x")

As you've seen, COUNTIF() can't take multiple ranges as the first
argument.

Do you have consistent data in Q1:R1, AF1:AG1, and AU1:AV1? For
instance, if they always had text or values that were different than
"x", you could shorten your formula to

=COUNTA(D1:BI1)-COUNTIF(D1:BI1,"x")-6

PROBLEM 2
is similar but I need the same COUNTA(D11,S1:AE1,AH1:AT1,AW1:BI1)
but need this time to discount "-" and "ill" so will need two COUNTIFs so I
need multiple ranges AND multiple COUNTIF

I shall manage them the l o n g way but it is always nice to refine things
and there is always a better way of doing it

Using the same logic as above:

=COUNTA(D1:BI1)-SUM(COUNTIF(D1:BI1,{"-","ill"})) - 6