Head scratchin for hours.... Help with a formula please...

[Bill Needham]

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]

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]

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!!!

 

[Joe User]

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]

Thanks I''l try that...

 

[Bill Needham]

YOU ROCK!!!!!! WORKS GGRREEAATT!!

 

[T. Valko]

the sum would be:

=SUMPRODUCT(--(A2:A5="AL"),B2:B5)

Or:

=SUMIF(A2:A5,"AL",B2:B5)