Prev: Drill down via page fields
Next: how to sum cells and ignore the #div/0! 's ?
From: Bill Needham on 24 Apr 2010 15:58
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
> > >
From: T. Valko on 24 Apr 2010 17:11
>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
news:6DBC364C-4904-47F8-BD9B-576988E8275B(a)microsoft.com...
> "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
>> >


First  |  Prev  | 
Pages: 1 2
Prev: Drill down via page fields
Next: how to sum cells and ignore the #div/0! 's ?