If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Countif with 2 or more data ranges in same column
Does Excel have a function that will allow me to count
only the data range for a specific title when there is more than one data range in the same column? I am using Excel 2002 on an XP OS. Refering to the example below here is what I am trying to accomplish: Column "A" contains the titles and column "B" contains the data. In this example the only 2 cells in column "A", except for the heading row, that are not empty are A2, which contains the title "ABC", and A8, which contains "XYZ". Which ever row the title in column "A" is in, the data for that title begins in column "B" and ends just before the empty cell as you go down column "B". Since ABC is in row 2 the data for ABC begins in cell B2. Since the first empty cell in column B is B6 the end of the data range for ABC is cell B5. The data range for XYZ starts at B8 and ends at B15. I am looking for a function that will allow me to count the number of cells in column B that have data entry for ABC separately and in addition to the data entry for XYZ. The amount of data cells in column B will vary each day for each title so I can't use a fixed range. On some days there are more than 2 titles so it would be necessary to count the data ranges for more than 2 titles. No matter how many titles the data will always be in column "B" and separated from the next title's data by empty cells. A B 1 Title Data 2 ABC 123 3 246 4 123 5 369 6 7 8 XYZ 321 9 123 10 333 11 331 12 678 13 333 14 244 15 543 Thanks in advance, Doug |
#2
|
|||
|
|||
Countif with 2 or more data ranges in same column
C1: RowIdx, a label.
C2, copied down: =IF(A2"",ROW(A2),"") This formula creates an additional column of data. E1 houses the lable Title. E2 houses ABC, a title. F1 houses the label Count. F2: =IF(ISNUMBER(G2),COUNT(INDEX(B:B,G2):INDEX(B:B,H2) ),"") Copy this down for additional titles in E. G1 houses the label From. G2, copied down: =MATCH(E2,A:A,0) H1 houses the label To. H2, copied down: =IF(LOOKUP(REPT("z",255),A:A)=E2,MATCH(9.999999999 99999E+307,B:B),MIN(INDEX( C:C,G2+1):INDEX(C:C,MATCH(9.99999999999999E+307,C: C)))-1) Note that the formulas in G2 and H2 calculate the subranges for titles in E. "Doug" wrote in message ... Does Excel have a function that will allow me to count only the data range for a specific title when there is more than one data range in the same column? I am using Excel 2002 on an XP OS. Refering to the example below here is what I am trying to accomplish: Column "A" contains the titles and column "B" contains the data. In this example the only 2 cells in column "A", except for the heading row, that are not empty are A2, which contains the title "ABC", and A8, which contains "XYZ". Which ever row the title in column "A" is in, the data for that title begins in column "B" and ends just before the empty cell as you go down column "B". Since ABC is in row 2 the data for ABC begins in cell B2. Since the first empty cell in column B is B6 the end of the data range for ABC is cell B5. The data range for XYZ starts at B8 and ends at B15. I am looking for a function that will allow me to count the number of cells in column B that have data entry for ABC separately and in addition to the data entry for XYZ. The amount of data cells in column B will vary each day for each title so I can't use a fixed range. On some days there are more than 2 titles so it would be necessary to count the data ranges for more than 2 titles. No matter how many titles the data will always be in column "B" and separated from the next title's data by empty cells. A B 1 Title Data 2 ABC 123 3 246 4 123 5 369 6 7 8 XYZ 321 9 123 10 333 11 331 12 678 13 333 14 244 15 543 Thanks in advance, Doug |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reversing Sign on Column of Data (Positive to Negative) (Negative to Postive) | M Stokes | Worksheet Functions | 1 | April 26th, 2004 04:33 PM |
Renaming data in a new column | CLR | Worksheet Functions | 0 | February 7th, 2004 09:01 PM |
Adjusting Chart source data when column is deleted | YBTM | Charts and Charting | 1 | January 29th, 2004 08:31 PM |
Matching column data in different worksheets | befuddledprof | Worksheet Functions | 2 | January 15th, 2004 08:46 PM |
count data in column | zchazman | Charts and Charting | 1 | January 6th, 2004 05:20 PM |