|
| 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
April 24th, 2010, 07:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Head scratchin for hours.... Help with a formula please...
1 workbook-54 sheets. One sheet per week. On that sheet I need a way to track
state mileage. 48 states. might be in a state 6 or 7 times and the all of the other states I am in then a running total for each state each week..... HELP!!!!!!! -- Bill Needham GSSComputers 
|
|
#2
April 24th, 2010, 08:06 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Head scratchin for hours.... Help with a formula please...
tried the =INDEX(A2:B5,MATCH("AL",A2:B5,0),2) works great for one entry for a
state. When I enter more mileage for that state it only returns the fist entry. I need it to total up the mileage for each state entry... -- Bill Needham GSSComputers "Bill Needham" wrote: 1 workbook-54 sheets. One sheet per week. On that sheet I need a way to track state mileage. 48 states. might be in a state 6 or 7 times and the all of the other states I am in then a running total for each state each week..... HELP!!!!!!! -- Bill Needham GSSComputers
|
|
#3
April 24th, 2010, 08:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Head scratchin for hours.... Help with a formula please...
I selected cell area to insert "AL 145" then I simply put the next entry in
the next row below "OH 247". Then mabey an additional "AL 123" there in lies the problem the field I have selected to show the total for "AL" only shows the first entry in the range. Thanks again!!! -- Bill Needham GSSComputers "Bill Needham" wrote: tried the =INDEX(A2:B5,MATCH("AL",A2:B5,0),2) works great for one entry for a state. When I enter more mileage for that state it only returns the fist entry. I need it to total up the mileage for each state entry... -- Bill Needham GSSComputers "Bill Needham" wrote: 1 workbook-54 sheets. One sheet per week. On that sheet I need a way to track state mileage. 48 states. might be in a state 6 or 7 times and the all of the other states I am in then a running total for each state each week..... HELP!!!!!!! -- Bill Needham GSSComputers
|
|
#4
April 24th, 2010, 08:37 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Head scratchin for hours.... Help with a formula please...
"Bill Needham" wrote:
tried the =INDEX(A2:B5,MATCH("AL",A2:B5,0),2) works great for one entry for a state. When I enter more mileage for that state it only returns the fist entry. I need it to total up the mileage for each state entry. I assume that A2:A5 contains the state and B2:B5 contains the mileage. Then the sum would be: =SUMPRODUCT(--(A2:A5="AL"),B2:B5) or =SUMPRODUCT((A2:A5="AL")*B2:B5) I prefer the first form because it works even if some of B2:B5 contains text, notably the null string (""). ----- original message ----- "Bill Needham" wrote: tried the =INDEX(A2:B5,MATCH("AL",A2:B5,0),2) works great for one entry for a state. When I enter more mileage for that state it only returns the fist entry. I need it to total up the mileage for each state entry... -- Bill Needham GSSComputers "Bill Needham" wrote: 1 workbook-54 sheets. One sheet per week. On that sheet I need a way to track state mileage. 48 states. might be in a state 6 or 7 times and the all of the other states I am in then a running total for each state each week..... HELP!!!!!!! -- Bill Needham GSSComputers
|
|
#5
April 24th, 2010, 08:52 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Head scratchin for hours.... Help with a formula please...
Thanks I''l try that...
-- Bill Needham GSSComputers "Joe User" wrote: "Bill Needham" wrote: tried the =INDEX(A2:B5,MATCH("AL",A2:B5,0),2) works great for one entry for a state. When I enter more mileage for that state it only returns the fist entry. I need it to total up the mileage for each state entry. I assume that A2:A5 contains the state and B2:B5 contains the mileage. Then the sum would be: =SUMPRODUCT(--(A2:A5="AL"),B2:B5) or =SUMPRODUCT((A2:A5="AL")*B2:B5) I prefer the first form because it works even if some of B2:B5 contains text, notably the null string (""). ----- original message ----- "Bill Needham" wrote: tried the =INDEX(A2:B5,MATCH("AL",A2:B5,0),2) works great for one entry for a state. When I enter more mileage for that state it only returns the fist entry. I need it to total up the mileage for each state entry... -- Bill Needham GSSComputers "Bill Needham" wrote: 1 workbook-54 sheets. One sheet per week. On that sheet I need a way to track state mileage. 48 states. might be in a state 6 or 7 times and the all of the other states I am in then a running total for each state each week..... HELP!!!!!!! -- Bill Needham GSSComputers
|
|
#6
April 24th, 2010, 08:58 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Head scratchin for hours.... Help with a formula please...
YOU ROCK!!!!!! WORKS GGRREEAATT!!
-- Bill Needham GSSComputers "Joe User" wrote: "Bill Needham" wrote: tried the =INDEX(A2:B5,MATCH("AL",A2:B5,0),2) works great for one entry for a state. When I enter more mileage for that state it only returns the fist entry. I need it to total up the mileage for each state entry. I assume that A2:A5 contains the state and B2:B5 contains the mileage. Then the sum would be: =SUMPRODUCT(--(A2:A5="AL"),B2:B5) or =SUMPRODUCT((A2:A5="AL")*B2:B5) I prefer the first form because it works even if some of B2:B5 contains text, notably the null string (""). ----- original message ----- "Bill Needham" wrote: tried the =INDEX(A2:B5,MATCH("AL",A2:B5,0),2) works great for one entry for a state. When I enter more mileage for that state it only returns the fist entry. I need it to total up the mileage for each state entry... -- Bill Needham GSSComputers "Bill Needham" wrote: 1 workbook-54 sheets. One sheet per week. On that sheet I need a way to track state mileage. 48 states. might be in a state 6 or 7 times and the all of the other states I am in then a running total for each state each week..... HELP!!!!!!! -- Bill Needham GSSComputers
|
|
#7
April 24th, 2010, 10:11 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Head scratchin for hours.... Help with a formula please...
the sum would be:
=SUMPRODUCT(--(A2:A5="AL"),B2:B5) Or: =SUMIF(A2:A5,"AL",B2:B5) -- Biff Microsoft Excel MVP "Joe User" joeu2004 wrote in message ... "Bill Needham" wrote: tried the =INDEX(A2:B5,MATCH("AL",A2:B5,0),2) works great for one entry for a state. When I enter more mileage for that state it only returns the fist entry. I need it to total up the mileage for each state entry. I assume that A2:A5 contains the state and B2:B5 contains the mileage. Then the sum would be: =SUMPRODUCT(--(A2:A5="AL"),B2:B5) or =SUMPRODUCT((A2:A5="AL")*B2:B5) I prefer the first form because it works even if some of B2:B5 contains text, notably the null string (""). ----- original message ----- "Bill Needham" wrote: tried the =INDEX(A2:B5,MATCH("AL",A2:B5,0),2) works great for one entry for a state. When I enter more mileage for that state it only returns the fist entry. I need it to total up the mileage for each state entry... -- Bill Needham GSSComputers "Bill Needham" wrote: 1 workbook-54 sheets. One sheet per week. On that sheet I need a way to track state mileage. 48 states. might be in a state 6 or 7 times and the all of the other states I am in then a running total for each state each week..... HELP!!!!!!! -- Bill Needham GSSComputers
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
