Prev: Drill down via page fields
Next: how to sum cells and ignore the #div/0! 's ?
From: Bill Needham on 24 Apr 2010 14:59
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

From: Bill Needham on 24 Apr 2010 15:06
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
>
From: Bill Needham on 24 Apr 2010 15:23
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
> >
From: Joe User on 24 Apr 2010 15:37
"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
> >
From: Bill Needham on 24 Apr 2010 15:52
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
> > >
 |  Next  |  Last
Pages: 1 2
Prev: Drill down via page fields
Next: how to sum cells and ignore the #div/0! 's ?