Solved

Posted on 2011-05-11

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$76,"=VALUE(LEFT(Sheet1!$G$7:$G$76,2))>=15",$F$7:$F$76,$C4)

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

16 Comments

=Countifs(Value(Left($G7:$

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

Here it is, explained:

=SUMPRODUCT(--(VALUE(LEFT(

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))>=

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

Correct?

Dave

=SUM(IF((IF(ISERROR(VALUE(

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

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,

Dave

=SUM(--(IF(ISNUMBER(LEFT($

confirm with

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

Dave

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 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

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.

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

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

Dave

Title | # Comments | Views | Activity |
---|---|---|---|

Updating Pivot Table within VBA | 5 | 29 | |

Excel VBA, find a string in a column, update a cell | 7 | 27 | |

rank minimum order | 9 | 19 | |

Excel -- increase row height through cell entry (via VBA) | 3 | 30 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**15** Experts available now in Live!