Cell Range Problem

[Xxeno Cide]

I am trying to have Excel count the number of cells that contain "10" in them within a given range. If every item in that given range has a 10 in it, I want it to say "You Win!" and if it doesn't I want it to display the number of cells with 10 in them out of the total number of cells.

This is the formula that should work:

=IF(COUNTIF((E4:E128,H4:H169,K4:K156,N4:N175,Q4:Q111,T4:T109,W4:W57,Z4:Z55,AC4:AC30,AF4:AF8,AI4:AI82),10)=1047,"You Win!",TEXT(COUNTIF((E4:E128,H4:H169,K4:K156,N4:N175,Q4:Q111,T4:T109,W4:W57,Z4:Z55,AC4:AC30,AF4:AF8,AI4:AI82),10),"0")&"/1047")

However, it returns a #VALUE! Error. I don't know why this formula won't work, because it works when I change the range to just a single column, but when I add multiple columns it no longer works.

 

[Ron Rosenfeld]

I am trying to have Excel count the number of cells that contain "10" in them within a given range. If every item in that given range has a 10 in it, I want it to say "You Win!" and if it doesn't I want it to display the number of cells with 10 in them out of the total number of cells.

This is the formula that should work:

=IF(COUNTIF((E4:E128,H4:H169,K4:K156,N4:N175,Q4:Q111,T4:T109,W4:W57,Z4:Z55,AC4:AC30,AF4:AF8,AI4:AI82),10)=1047,"You Win!",TEXT(COUNTIF((E4:E128,H4:H169,K4:K156,N4:N175,Q4:Q111,T4:T109,W4:W57,Z4:Z55,AC4:AC30,AF4:AF8,AI4:AI82),10),"0")&"/1047")

However, it returns a #VALUE! Error. I don't know why this formula won't work, because it works when I change the range to just a single column, but when I add multiple columns it no longer works.

I don't think you can use non-contiguous ranges in this manner.

Here are two possible solutions:\
1. Use a separate COUNTIF for each contiguous range, and sum them:

=sum(countif(e4:e128,10),countif(h4:h169,10), ...

2. Use a VBA User Defined Function (UDF) which does the same thing, but might be easier to enter on your worksheet

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=NonContigCountIf(10,E4:E128,H4:H169,K4:K156,N4:N175,Q4:Q111,T4:T109,W4:W57,Z4:Z55,AC4:AC30,AF4:AF8,AI4:AI82)

in some cell. Note that for this UDF, I chose to have the criteria entered as the first argument. It makes coding the UDF a bit more straightforward. In the UDF, ParamArray allows the input of any number of arguments, but must appear last in the entry list.

==============================================
Option Explicit
Function NonContigCountIf(sCriteria As Variant, ParamArray Rng() As Variant) As Long
Dim v As Variant
Dim lTemp As Long
With WorksheetFunction
For Each v In Rng
lTemp = lTemp + .CountIf(v, sCriteria)
Next v
End With
NonContigCountIf = lTemp

End Function
===============================