Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find First and Last Row Number in Column
Hi All,
I have the following data which will always be sorted/grouped. I basically want to sum all the A's, B's, and C's values (like a subtotal) Column Value A 2 A 3 A 1 B 3 B 4 B 5 C 6 C 1 C 9 I want to find the the First and last row number A and also for B, so Start A = 1, End A = 3 Start B = 4, End B = 6 Start C = 7, End C = 9 This issue I have is the column values can differ. For example, Column A A A C C C So, Start A = 1, End A = 3 Start C = 4, End C = 6 Since I know the start and end of each grouping, I can then sum them. If the column alwas had A,B,and C, then I know how to do this, however, since it is dynamic, I am not sure how to write code dynamic enough to handle this. TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find First and Last Row Number in Column
If the end results are totaling numbers associated with A, B, and C, why not
use a pivot table? This site will get you started: http://www.contextures.com/xlPivot01.html Don Pistulka "CST" wrote in message om... Hi All, I have the following data which will always be sorted/grouped. I basically want to sum all the A's, B's, and C's values (like a subtotal) Column Value A 2 A 3 A 1 B 3 B 4 B 5 C 6 C 1 C 9 I want to find the the First and last row number A and also for B, so Start A = 1, End A = 3 Start B = 4, End B = 6 Start C = 7, End C = 9 This issue I have is the column values can differ. For example, Column A A A C C C So, Start A = 1, End A = 3 Start C = 4, End C = 6 Since I know the start and end of each grouping, I can then sum them. If the column alwas had A,B,and C, then I know how to do this, however, since it is dynamic, I am not sure how to write code dynamic enough to handle this. TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find First and Last Row Number in Column
sumA =application.Sumif(Columns(1),"A",Columns(2))
SumB = application.Sumif(Columns(1),"B",Columns(2)) SumC = application.Sumif(Columns(1),"C",Columns(2)) will do it without sorting or grouping. Regards, Tom Ogilvy "CST" wrote in message om... Hi All, I have the following data which will always be sorted/grouped. I basically want to sum all the A's, B's, and C's values (like a subtotal) Column Value A 2 A 3 A 1 B 3 B 4 B 5 C 6 C 1 C 9 I want to find the the First and last row number A and also for B, so Start A = 1, End A = 3 Start B = 4, End B = 6 Start C = 7, End C = 9 This issue I have is the column values can differ. For example, Column A A A C C C So, Start A = 1, End A = 3 Start C = 4, End C = 6 Since I know the start and end of each grouping, I can then sum them. If the column alwas had A,B,and C, then I know how to do this, however, since it is dynamic, I am not sure how to write code dynamic enough to handle this. TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find First and Last Row Number in Column
If the list is in A1 to A9 and values are in B1:B9
then this formula will do what you ask for. =SUMPRODUCT((A1:A9="A")*1,B1:B9) Replace the "A" with "B" to sum the rows with B in column A... Wendel "CST" skrev i meddelandet om... Hi All, I have the following data which will always be sorted/grouped. I basically want to sum all the A's, B's, and C's values (like a subtotal) Column Value A 2 A 3 A 1 B 3 B 4 B 5 C 6 C 1 C 9 I want to find the the First and last row number A and also for B, so Start A = 1, End A = 3 Start B = 4, End B = 6 Start C = 7, End C = 9 This issue I have is the column values can differ. For example, Column A A A C C C So, Start A = 1, End A = 3 Start C = 4, End C = 6 Since I know the start and end of each grouping, I can then sum them. If the column alwas had A,B,and C, then I know how to do this, however, since it is dynamic, I am not sure how to write code dynamic enough to handle this. TIA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find First and Last Row Number in Column
Hi,
You might want to use the SUMIF function... =SUMIF(A:A,"A",B:B) will sum anything in column A that is "A" using the values in column B. Hope this helps... Chong Moua -----Original Message----- Hi All, I have the following data which will always be sorted/grouped. I basically want to sum all the A's, B's, and C's values (like a subtotal) Column Value A 2 A 3 A 1 B 3 B 4 B 5 C 6 C 1 C 9 I want to find the the First and last row number A and also for B, so Start A = 1, End A = 3 Start B = 4, End B = 6 Start C = 7, End C = 9 This issue I have is the column values can differ. For example, Column A A A C C C So, Start A = 1, End A = 3 Start C = 4, End C = 6 Since I know the start and end of each grouping, I can then sum them. If the column alwas had A,B,and C, then I know how to do this, however, since it is dynamic, I am not sure how to write code dynamic enough to handle this. TIA . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find First and Last Row Number in Column
Sweet!!! This worked! Thank you sooooooo much!
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FInd common data in one column then add number in adjacent column | Excel Worksheet Functions | |||
Find previous number and find next number in column | Excel Discussion (Misc queries) | |||
How to find what number in Column A is not included in Column B? | Excel Discussion (Misc queries) | |||
Find max Row() number for a value in column | Excel Worksheet Functions | |||
How do I find the first value in a column less than a number? | Excel Worksheet Functions |