# COUNTIF Formula help

Hello,

I am trying to extract the first two digits from the cells in sheet 1 column G which has text like this - 15 Late.
Then I am trying to count the occurrences of the numbers greater than or equal to 15 within the specified range. However I can’t get my head around converting the 15 into a number format within the COUNTIF formula in order to actually count it.

Here is my formula :

=COUNTIFS(Sheet1!\$G\$7:\$G\$76,"=VALUE(LEFT(Sheet1!\$G\$7:\$G\$76,2))>=15",\$F\$7:\$F\$76,\$C4)
###### Who is Participating?

Microsoft MVP ExcelCommented:
This array formula does not error when the values are text or empty

=SUM(--(IF(ISNUMBER(LEFT(\$G7:\$G\$76,2)+0),LEFT(\$G7:\$G\$76,2)+0,0)>=15)*(\$F7:\$F76=\$C\$4))

confirm with Ctrl-Shift-Enter
0

Application DeveloperCommented:
Do you have an example of the spreadsheet?
0

Commented:
This will work:

=SUMPRODUCT(--(VALUE(LEFT(Sheet1!\$G\$7:\$G\$76,2))>=15)*(\$F\$7:\$F\$76=\$C\$4))

Dave
0

Microsoft MVP ExcelCommented:
The Sumproduct formula will work, but only if all cells in the range start with two characters that can be interpreted as numbers. If any of the cells start with alpha characters or are blank, then the formula will error.
0

Commented:
Unfortunately, you cannot put a function in the middle of a criteria range with the COUNTIFS function, otherwise your formula would look like:

=Countifs(Value(Left(\$G7:\$G\$76,2)),">=15", etcetera).

But with the SUMPRODUCT, you can do I believe anything that COUNTIFS can, and more:

Here it is, explained:

=SUMPRODUCT(--(VALUE(LEFT(\$G\$7:\$G\$72,2))>=15)*(\$F\$7:\$F\$76=\$C\$4))

the first part - the "--"  resolves everything to a 1 or zero, allowing SUMPRODUCT to add the number of matches based on the unary increment.  Thus like COUNTIFS...

The VALUE(LEFT\$G\$7:\$G\$72,2))>=15 returns an array of TRUE/FALSES as a result of that comparison

The * just tells SUMPRODUCT TO MULTIPLY THE TRUE FALSES ACROSS, so both sets of conditions have to match to return a 1 or TRUE.

the \$F\$7:\$F\$76=\$C\$4 returns an array of TRUE/FALSES as a result of comparing the first array to the value in the cell \$C\$4.

And you can keep adding conditions in that fashion...

Dave
0

Commented:
@Teylyn - the SUMPRODUCT would not be at fault - it would be the VALUE(LEFT(xxx,2)) equation.  which, currently, doesn't appear to be in question, but good to note.

Correct?

Dave
0

EngineerCommented:
To correct that you can use

=SUM(IF((IF(ISERROR(VALUE(LEFT(Sheet1!\$G\$70:\$G\$76,2))),0,VALUE(LEFT(Sheet1!\$G\$70:\$G\$76,2)))>=15)*(\$F\$70:\$F\$76=\$C4),1))

entered as an ARRAY FORMULA (shift-ctrl-enter)
0

Commented:
That could be fairly inefficient, depending on how many of these are in the spreadsheet... If more than one.

And probably doesn't solve the problem of getting an answer - rather, it could be a data correction being needed....  otherwise, you get the answer you need, or NOTHING.  It would be all or none - so just like getting an #VALUE - all or none.

Correct me if I misstated - but that's my take...

How to get the formula to SKIP any that could raise an error might be the most appropriate, if error correction is needed at all, yes?

Dave
0

Microsoft MVP ExcelCommented:
Dave, what was your last comment referring to? I'm a bit lost.

What is inefficient? An array formula? Not noticably slower than a Sumproduct.
0

Commented:
I like that :)

Dave
0

Commented:
We can take this to the Experts discussion, but yes, array formulas in my opinion are significantly slower.

Dave
0

Microsoft MVP ExcelCommented:
Sumproduct also builds an array and evaluates it.

Sumif and Sumifs are considerably faster than both Sumproduct and Array formulas, but there is not much difference between Sumproduct and a sum(if()) array formula.
0

Commented:
I posted in Experts discussion as to not wade the Asker down with this discussion...

I like your error correction and if its needed, that's the way I would do it as well - though I was considering writing a UDF to perform the VALUE function :)

Cheers,

Dave
0

Author Commented:
Thanks Teylyn and Dave for your help!

Teylyn - Your array formula works perfectly thank you!! but I have given Dave 150 points because he explained why I couldn't actually use a COUNTIF formula in this instance and how SUMIF formulas work which has helped boost my excel knowledge a little more.

Thank you both though for your fast replies.
0

Author Commented:
Sorry to open this up again but I just noticed that the reason Daves SUMIF formula didn't work was because he accidently left off Sheet1!. Once I added this to the formula and tested it on my sheet the formula also works perfectly!!

SORRY DAVE I OWE YOU MORE POINTS - IF I COULD I WOULD!!!
0

Commented:
Yes it does - sorry about that - I was documenting in my second post and was typing right in the window so thought to "simplify" so you'd understand the function more clearly.

In future for all the right reasons, you can request attention on a question and redivvy points or make other pertinent changes, as appropraite.

I do NOT suggest you need to do that.  This is a collaborative response and as a result you got the BEST solution, due to the error correction Teylyn put forward.  And, I'm getting an education on the relative efficiency of the different functions as well.

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.