Ignore Text in a Cell and Sum Only the Values of the First 3 Non Zero Numbers

I assume that you want the total of column D to be 4+7+9=20?
Is there some kind of system in the text/number combinations. E.g. always the same number of text characters or always a number in a certain range (e.g. 0-9).
If not then a small macro will be necessary. Would that be a problem for you?

I don't think it can be done with a formula.
This function which you can use in a formula counts the first n non-zero cells in a range.
Usage is

Formula:

Please Login or Register  to view this content.

First parameter is the range and the second is the number of non-zero cells to count (to make it flexible)

Add this function to a module in your workbook

Please Login or Register  to view this content.

It's also added to the attached workbook.

Two non-VBA solutions.


With th No. of cells to be counted in E2 (can be hard-coded)

1. With a helper column, in B2, copied down:
=COUNT(A$2:A2)

In F2, copied, down:
=SUM($A2:INDEX($A2:$A12,MATCH(E2,$B2:$B12)))

2. More complex, but no helper, an array formula, copied down:
=SUM(OFFSET($A2,0,0,SMALL(ISNUMBER($A2:$A12)*ROW($A2:$A12)-ROW($A2)+1,ROWS($A2:$A12)-COUNT($A2:$A12)+MIN(E2,COUNT($A2:$A12)))))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Ignore the above. I completely misread the request

It doesn't ignore zeros was the main problem. Text/numbers add another level of complexity: well beyond me!! Go with VBA offered earlier.

Oops! Beginner error
The variable (x) used to collect the numeric part of the cell was initialized at the start of the procedure, but should be initialized for each cell.
Change is marked red.

Please Login or Register  to view this content.

Good to here that.
If that solved your problem pls mark the thread SOLVED (see Thread Tools in the menu above).
Thx for the rep