COINTIFS question

[John_Whin]

I've got this formula

=COUNTIFS('Scoring Input'!F:F,"NSH",'Scoring Input'!C:C,J25)

instead of looking for "NSH" I want it to countifs NOT "NSH"

There are 3 characters in each cell in the column 'Scoring Input'!F:F , so whether it is "MIN", "WPG" "CAR" etc I want it to sum all of those excluding "NSH".

Thanks

 

[Fluff]

How about
=COUNTIFS('Scoring Input'!F:F,"<>NSH",'Scoring Input'!C:C,J25)
Or, if you want to avoid blanks
=COUNTIFS('Scoring Input'!F:F,"<>NSH",'Scoring Input'!F:F,"?*",'Scoring Input'!C:C,J25)

 

[jtakw]

Hi,

2 things...

1. If you use Entire Column references, you'll Probably get erroneous results using this:

=COUNTIFS('Scoring Input'!F:F,"NSH",'Scoring Input'!C:C,J25)

So, you'll need to use Actual ranges (The actual size of your data), instead of F:F, and C:C

2. Or, you can SUM the other COUNTs, like this:

=SUM(COUNTIFS('Scoring Input'!F:F,{"MIN","WPG","CAR"},'Scoring Input'!C:C,J25))

Edit: Reason regarding point 1 above, Excel has 1048576 rows, unless your Column A is Completely filled with data, method 1 above using Entire column references will Also count the Blanks.

 

[jtakw]

Hi,

2 things...

1. If you use Entire Column references, you'll Probably get erroneous results using this:

=COUNTIFS('Scoring Input'!F:F,"NSH",'Scoring Input'!C:C,J25)

So, you'll need to use Actual ranges (The actual size of your data), instead of F:F, and C:C

2. Or, you can SUM the other COUNTs, like this:

=SUM(COUNTIFS('Scoring Input'!F:F,{"MIN","WPG","CAR"},'Scoring Input'!C:C,J25))

Edit: Reason regarding point 1 above, Excel has 1048576 rows, unless your Column A is Completely filled with data, method 1 above using Entire column references will Also count the Blanks.

Too late to edit, for Point 1 above I meant don't use this:

=COUNTIFS('Scoring Input'!F:F,"<>NSH",'Scoring Input'!C:C,J25)

 

[John_Whin]

Thanks Fluff et al. I had tried =COUNTIFS('Scoring Input'!F:F,<>"NSH",'Scoring Input'!C:C,J25)
instead of
=COUNTIFS('Scoring Input'!F:F,"<>NSH",'Scoring Input'!C:C,J25)

I'm good now

 

[Fluff]

Glad we could help & thanks for the feedback

 

[jtakw]

Are you Sure you're getting the correct result using entire column references ?

Anything that's Not NSH will get counted...including EMPTY and "" (formula blank) cells...

 

[Fluff]

Anything that's Not NSH will get counted...including EMPTY and "" (formula blank) cells...

That willonly happen if the relevant row in col C = the value in J25

 

[jtakw]

That's true.