| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counta function question
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counta function question
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) "Karen McKenzie" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counta function question
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 -- Brevity is the soul of wit. "Karen McKenzie" wrote: 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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counta function question
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? "Bob Phillips" wrote: 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) "Karen McKenzie" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counta function question
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" "Dave F" wrote: 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 -- Brevity is the soul of wit. "Karen McKenzie" wrote: 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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counta function question
Apologies Dave,
I meant to say there will be no blank entries "Dave F" wrote: 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 -- Brevity is the soul of wit. "Karen McKenzie" wrote: 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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counta function question
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" wrote: 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) "Karen McKenzie" wrote in message ... 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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counta function question
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" wrote in message ... 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" wrote: 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) "Karen McKenzie" wrote in message ... 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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counta function question
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" wrote: 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" wrote in message ... 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" wrote: 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) "Karen McKenzie" wrote in message ... 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? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counta function question
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) "Karen McKenzie" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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) "Karen McKenzie" wrote in message ... 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? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counta function question
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,"") -- Regards Roger Govier "Karen McKenzie" wrote in message ... 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? "Bob Phillips" wrote: 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) "Karen McKenzie" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average function question | Excel Worksheet Functions | |||
| NOW function question | Excel Worksheet Functions | |||
| clock | Excel Worksheet Functions | |||
| Dynamic Function Reference Question | Excel Discussion (Misc queries) | |||
| COUNTA Function not working =COUNTA(C3:C69,"NH") | Excel Worksheet Functions | |||
Linear Mode
