Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# COUNTIF Formula help

Posted on 2011-05-11
Medium Priority
478 Views
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)
0
Question by:vegas86
• 8
• 4
• 2
• +2

LVL 15

Expert Comment

ID: 35743306
Do you have an example of the spreadsheet?
0

LVL 42

Expert Comment

ID: 35743323
This will work:

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

Dave
0

LVL 50

Expert Comment

ID: 35743343
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

LVL 42

Assisted Solution

dlmille earned 600 total points
ID: 35743344
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

LVL 42

Expert Comment

ID: 35743347
@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

LVL 43

Expert Comment

ID: 35743374
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

LVL 42

Expert Comment

ID: 35743384
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

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1400 total points
ID: 35743421
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

LVL 50

Expert Comment

ID: 35743432
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

LVL 42

Expert Comment

ID: 35743435
I like that :)

Dave
0

LVL 42

Expert Comment

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

Dave
0

LVL 50

Expert Comment

ID: 35743445
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

LVL 42

Expert Comment

ID: 35743451
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 Closing Comment

ID: 35743481
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 Comment

ID: 35743526
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

LVL 42

Expert Comment

ID: 35743658
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
###### Suggested Courses
Course of the Month11 days, 9 hours left to enroll