vegas86
asked on
COUNTIF Formula help
Hello,
Can someone please help me understand what I am doing wrong with the below formula?
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$7
Can someone please help me understand what I am doing wrong with the below formula?
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$7
Juan Ocasio
Do you have an example of the spreadsheet?
This will work:
=SUMPRODUCT(--(VALUE(LEFT(
Dave
=SUMPRODUCT(--(VALUE(LEFT(
Dave
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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
Correct?
Dave
To correct that you can use
=SUM(IF((IF(ISERROR(VALUE(
entered as an ARRAY FORMULA (shift-ctrl-enter)
=SUM(IF((IF(ISERROR(VALUE(
entered as an ARRAY FORMULA (shift-ctrl-enter)
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
What is inefficient? An array formula? Not noticably slower than a Sumproduct.
I like that :)
Dave
Dave
We can take this to the Experts discussion, but yes, array formulas in my opinion are significantly slower.
Dave
Dave
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.
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.
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
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
ASKER
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.
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.
ASKER
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!!!
SORRY DAVE I OWE YOU MORE POINTS - IF I COULD I WOULD!!!
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
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