Counta function question

[Karen McKenzie]

I have an extract of data that shows detail by item, then an overall result.
I want to use the counta function to count the number of non blank entries
between each entry called result ie:

Equipment Amount Count
21053 192.35
21064 207.30
Result 2
60987 303.20
12156 97.20
30752 1426.67
Result 3

Can this be done?

 

[Bob Phillips]

Put this in C2 and copy down

=IF(A2="Result",ROW()-MAX(MIN(IF($A$1:$A1="Result",ROW($A$1:$A1))),1)-1,"")

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Dave F]

How about =COUNT(range)-COUNTBLANK(range) This subtracts the count of blanks
from the total count in the range, which, by implication, gives you the
number of non-blank entries.

Dave

 

[Karen McKenzie]

Thanks Bob,
This works fine if the data is where you said


21053 £ 192.35
21064 £ 407.36
Result £ 599.71 2
60987 £ 352.61
60988 £ 1,301.75
61254 £ 2,184.32
67225 £ 552.76
Result £ 4,391.44 4



My header row is 21, and my data starts in column E on line 22. I have
therefore adjusted the formula to read:

=IF(E22="Result",ROW()-MAX(MIN(IF($E$21:$E21="Result",ROW($E$21:$E21))),1)-1,"")

My results are calculating incorrectly as follows

21053 £ 192.35
21064 £ 407.36
Result £ 599.71 22
60987 £ 352.61
60988 £ 1,301.75
61254 £ 2,184.32
67225 £ 552.76
Result £ 4,391.44 4

Can you tell me where I'm going wrong?

 

[Karen McKenzie]

Hi Dave,

There will be no non-blank entries in the column showing result. The
entries in this column are not in number format hence I thought of counta.
The range will vary depending on how many lines of data there are between
each occurance of "result"

 

[Karen McKenzie]

Apologies Dave,

I meant to say there will be no blank entries

 

[Karen McKenzie]

Hi Bob,

I've just deleted the data that I had at the top of my spreadsheet so that
my data starts in row 2. The first two results calculate correctly but after
that it is incorrect.

Equipment Quantity Price unit Amount
21053 4.000 EA 315.370 £ 192.35
21064 18.000 EA 311.730 £ 407.36
Result 22.000 EA 627.100 £ 599.71 2
60987 * 332.790 £ 352.61
60988 63.000 EA 992.040 £ 1,301.75
61254 * 1,740.120 £ 2,184.32
67225 * 580.100 £ 552.76
Result * 3,645.050 £ 4,391.44 4
62619 * 497.300 £ 396.69
62621 * 1,000.150 £ 1,152.65
62625 * 149.060 £ 169.35
Result * 1,646.510 £ 1,718.69 8

 

[Bob Phillips]

I am struggling to see what data goes in which column. It is not the same as
your original post.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Karen McKenzie]

Hi Bob,

I simplified the original detail so that only the relevant column and one
other were showing.

My spreadsheet runs from column A to H and will vary in number of rows but
is currently about 15000.

The data starts on row 22

ID Cost Center Veh Type Equipment Quantity Price unit Amount
17 FG01/17AA Dennis Javelin 21053 4 315.37 £ 192.35
21064 18.000 EA 311.730 £ 407.36
Result 22.000 EA 627.100 £ 599.71
Dennis Lance 60987 * 332.790 £ 352.61
60988 63.000 EA 992.040 £ 1,301.75
61254 * 1,740.120 £ 2,184.32
67225 * 580.100 £ 552.76
Result * 3,645.050 £ 4,391.44
Leyland Lynx 62619 * 497.300 £ 396.69
62621 * 1,000.150 £ 1,152.65
62625 * 149.060 £ 169.35
Result * 1,646.510 £ 1,718.69
Leyland Tiger 22045 * 229.010 £ 198.95
Result * 229.010 £ 198.95

 

[Bob Phillips]

I still don't see it. I cannot differentiate what goes in which column with
your data, and I have no idea what the * are.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Roger Govier]

Hi Karen

If you are starting on row 21, then I think you need to amend your
amendment of Bob's formula to reflect that within the MAX function

=IF(E22="Result",ROW()-MAX(MIN(IF($E$21:$E21="Result",
ROW($E$21:$E21))),1)-1,"")
^^^
change to
=IF(E22="Result",ROW()-MAX(MIN(IF($E$21:$E21="Result",
ROW($E$21:$E21))),21)-1,"")