Suppose you have 2 columns of data. Data gets added to the columns
frequently.

A B
xyz 4
abc 3
jkl 5
xyz 2
abc 1
def 4
abc 3

I need to calculate the total number in column B for the last 2
entries. Thus, if looking for "abc", the answer would be 4 (3+1),
since the abc got a 3 the last entry and 1 for the previous entry.
Using the same formula for xyz would return 6 (2 +4).

Any ideas on how I can accomplish this?

I'm not sure the example you've given is correct. If you total the numbers
in column B for "abc" from column A, then the total should be 7. I'm
guessing this is what you want. If 7 is the correct answer, then maybe:

=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A"&C OUNTA(A:A)),B:B)

For the second to last entry in column A:

=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A"&C OUNTA(A:A)-1),B:B)


HTH,
Paul

wrote in message
ups.com...
Suppose you have 2 columns of data. Data gets added to the columns
frequently.

A B
xyz 4
abc 3
jkl 5
xyz 2
abc 1
def 4
abc 3

I need to calculate the total number in column B for the last 2
entries. Thus, if looking for "abc", the answer would be 4 (3+1),
since the abc got a 3 the last entry and 1 for the previous entry.
Using the same formula for xyz would return 6 (2 +4).

Any ideas on how I can accomplish this?

No, I have it right; probably just didn't explain it well enough. For
the "abc" example, I want the formula to add the appropriate amount in
column B for the latest 2 references to "abc". So in my example below,
the latest abc reference refers to 3 in column B. The immediately
preceding reference to "abc" refers to 1 in column B. 3+1 = 4 and
thats the result that would be accurate.

On Nov 27, 3:42 pm, "PCLIVE" wrote:
I'm not sure the example you've given is correct. If you total the numbers
in column B for "abc" from column A, then the total should be 7. I'm
guessing this is what you want. If 7 is the correct answer, then maybe:

=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A"&C OUNTA(A:A)),B:B)

For the second to last entry in column A:

=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A"&C OUNTA(A:A)-1),B:B)

HTH,
Paul

wrote in oglegroups.com...

Suppose you have 2 columns of data. Data gets added to the columns
frequently.

A B
xyz 4
abc 3
jkl 5
xyz 2
abc 1
def 4
abc 3

I need to calculate the total number in column B for the last 2
entries. Thus, if looking for "abc", the answer would be 4 (3+1),
since the abc got a 3 the last entry and 1 for the previous entry.
Using the same formula for xyz would return 6 (2 +4).

Any ideas on how I can accomplish this?

Ok. I guess what was confusing is that there are three entries in column A
that contain "abc". If you add the corresponding numbers for all three,
then the total would be 7. Since you confirmed the total should be 4, then
I have to assume that you do not want to count the value next to the last
entry. Is that correct?
If so, then maybe:
=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)-1),INDIRECT("A"&COUNTA(A:A)),B:B)

Another thing that was confusing is that the next to the last entry in
column A, is "def". Based on your original explanation and example, it
would appear that you were saying that the next to last entry in column A
should be "xyz".
=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)-2),INDIRECT("A"&COUNTA(A:A)-1),B:B)

Key questions:
-Will only the last two entries be searched?
-Should either of the numbers next to those last two entries be included in
the total? I'm thinking no.
-Was the next to last entry in your example supposed to be "xyz"?

I think the above formulas may work for you.

HTH,
Paul

wrote in message
ups.com...
No, I have it right; probably just didn't explain it well enough. For
the "abc" example, I want the formula to add the appropriate amount in
column B for the latest 2 references to "abc". So in my example below,
the latest abc reference refers to 3 in column B. The immediately
preceding reference to "abc" refers to 1 in column B. 3+1 = 4 and
thats the result that would be accurate.

On Nov 27, 3:42 pm, "PCLIVE" wrote:
I'm not sure the example you've given is correct. If you total the
numbers
in column B for "abc" from column A, then the total should be 7. I'm
guessing this is what you want. If 7 is the correct answer, then maybe:

=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A"&C OUNTA(A:A)),B:B)

For the second to last entry in column A:

=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A"&C OUNTA(A:A)-1),B:B)

HTH,
Paul

wrote in
oglegroups.com...

Suppose you have 2 columns of data. Data gets added to the columns
frequently.

A B
xyz 4
abc 3
jkl 5
xyz 2
abc 1
def 4
abc 3

I need to calculate the total number in column B for the last 2
entries. Thus, if looking for "abc", the answer would be 4 (3+1),
since the abc got a 3 the last entry and 1 for the previous entry.
Using the same formula for xyz would return 6 (2 +4).

Any ideas on how I can accomplish this?

I'm sorry for being so confusing here. Rather than responding to your
message, let me just restate my problem.

My spreadsheet contains the following 2 columns [Animal} and [Points},
with the following data that will be expanded. Suppose each animal
plays a game, so in the following chart 9 games have been played. In
the first game, Monkey scored 10 points; in the next game Gorilla
scosred 20 point.

[Animal] [Points}
Monkey 10
Gorilla 20
Dog 30
Monkey 15
Dog 20
Cat 10
Monkey 30
Gorilla 20
Gorilla 30

I am looking to find out how many points Gorilla scored in the last 2
games that Gorilla played. In this case, the answer would be 50. If
the variable was Monkey, instead of Gorilla, the answer would be 45.

I need a formula that finds the last 2 scores for the animal I am
analyzing.

Thanks again.

PCLIVE (RemoveThis) wrote:
I'm not sure the example you've given is correct. If you total the numbers
in column B for "abc" from column A, then the total should be 7. I'm
guessing this is what you want. If 7 is the correct answer, then maybe:

=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A"&C OUNTA(A:A)),B:B)

For the second to last entry in column A:

=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A"&C OUNTA(A:A)-1),B:B)


HTH,
Paul

wrote in message
ups.com...
Suppose you have 2 columns of data. Data gets added to the columns
frequently.

A B
xyz 4
abc 3
jkl 5
xyz 2
abc 1
def 4
abc 3

I need to calculate the total number in column B for the last 2
entries. Thus, if looking for "abc", the answer would be 4 (3+1),
since the abc got a 3 the last entry and 1 for the previous entry.
Using the same formula for xyz would return 6 (2 +4).

Any ideas on how I can accomplish this?

Ok.

With C1 as the team name you want the last two game totals, try this:

=IF(COUNTIF(A:A,C1)2,SUMIF(INDIRECT("A"&(MATCH(C1 ,A:A,0)+1)&":A"&COUNTA(A:A)),C1,INDIRECT("B"&(MATC H(C1,A:A,0)+1)&":B"&COUNTA(A:A))),SUMIF(INDIRECT(" A1:A"
& COUNTA(A:A)),C1))

HTH,
Paul


wrote in message
ups.com...
I'm sorry for being so confusing here. Rather than responding to your
message, let me just restate my problem.

My spreadsheet contains the following 2 columns [Animal} and [Points},
with the following data that will be expanded. Suppose each animal
plays a game, so in the following chart 9 games have been played. In
the first game, Monkey scored 10 points; in the next game Gorilla
scosred 20 point.

[Animal] [Points}
Monkey 10
Gorilla 20
Dog 30
Monkey 15
Dog 20
Cat 10
Monkey 30
Gorilla 20
Gorilla 30

I am looking to find out how many points Gorilla scored in the last 2
games that Gorilla played. In this case, the answer would be 50. If
the variable was Monkey, instead of Gorilla, the answer would be 45.

I need a formula that finds the last 2 scores for the animal I am
analyzing.

Thanks again.

PCLIVE (RemoveThis) wrote:
I'm not sure the example you've given is correct. If you total the
numbers
in column B for "abc" from column A, then the total should be 7. I'm
guessing this is what you want. If 7 is the correct answer, then maybe:

=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A"&C OUNTA(A:A)),B:B)

For the second to last entry in column A:

=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A"&C OUNTA(A:A)-1),B:B)


HTH,
Paul

wrote in message
ups.com...
Suppose you have 2 columns of data. Data gets added to the columns
frequently.

A B
xyz 4
abc 3
jkl 5
xyz 2
abc 1
def 4
abc 3

I need to calculate the total number in column B for the last 2
entries. Thus, if looking for "abc", the answer would be 4 (3+1),
since the abc got a 3 the last entry and 1 for the previous entry.
Using the same formula for xyz would return 6 (2 +4).

Any ideas on how I can accomplish this?

Ok, that only works if there were no more than three games played.
Additionally, if two or less were played, the formula had to be modified as
follows.

=IF(COUNTIF(A:A,C1)2,SUMIF(INDIRECT("A"&(MATCH(C1 ,A:A,0)+1)&":A"&COUNTA(A:A)),C1,INDIRECT("B"&(MATC H(C1,A:A,0)+1)&":B"&COUNTA(A:A))),SUMIF(INDIRECT(" A1:A"
& COUNTA(A:A)),C1,B:B))

However, this still doesn't work if more than three games were played.
Maybe someone else can have a go at it.

Good luck.
Paul


"PCLIVE" wrote in message
...
Ok.

With C1 as the team name you want the last two game totals, try this:

=IF(COUNTIF(A:A,C1)2,SUMIF(INDIRECT("A"&(MATCH(C1 ,A:A,0)+1)&":A"&COUNTA(A:A)),C1,INDIRECT("B"&(MATC H(C1,A:A,0)+1)&":B"&COUNTA(A:A))),SUMIF(INDIRECT(" A1:A"
& COUNTA(A:A)),C1))

HTH,
Paul


wrote in message
ups.com...
I'm sorry for being so confusing here. Rather than responding to your
message, let me just restate my problem.

My spreadsheet contains the following 2 columns [Animal} and [Points},
with the following data that will be expanded. Suppose each animal
plays a game, so in the following chart 9 games have been played. In
the first game, Monkey scored 10 points; in the next game Gorilla
scosred 20 point.

[Animal] [Points}
Monkey 10
Gorilla 20
Dog 30
Monkey 15
Dog 20
Cat 10
Monkey 30
Gorilla 20
Gorilla 30

I am looking to find out how many points Gorilla scored in the last 2
games that Gorilla played. In this case, the answer would be 50. If
the variable was Monkey, instead of Gorilla, the answer would be 45.

I need a formula that finds the last 2 scores for the animal I am
analyzing.

Thanks again.

PCLIVE (RemoveThis) wrote:
I'm not sure the example you've given is correct. If you total the
numbers
in column B for "abc" from column A, then the total should be 7. I'm
guessing this is what you want. If 7 is the correct answer, then maybe:

=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A"&C OUNTA(A:A)),B:B)

For the second to last entry in column A:

=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A"&C OUNTA(A:A)-1),B:B)


HTH,
Paul

wrote in message
ups.com...
Suppose you have 2 columns of data. Data gets added to the columns
frequently.

A B
xyz 4
abc 3
jkl 5
xyz 2
abc 1
def 4
abc 3

I need to calculate the total number in column B for the last 2
entries. Thus, if looking for "abc", the answer would be 4 (3+1),
since the abc got a 3 the last entry and 1 for the previous entry.
Using the same formula for xyz would return 6 (2 +4).

Any ideas on how I can accomplish this?

Thanks Paul. Yes, I definitely need more than 3 games. It needs to
work no matter how many games have been played. I hope you or someone
has a solution, cuz I'm stuck.
PCLIVE (RemoveThis) wrote:
Ok, that only works if there were no more than three games played.
Additionally, if two or less were played, the formula had to be modified as
follows.

=IF(COUNTIF(A:A,C1)2,SUMIF(INDIRECT("A"&(MATCH(C1 ,A:A,0)+1)&":A"&COUNTA(A:A)),C1,INDIRECT("B"&(MATC H(C1,A:A,0)+1)&":B"&COUNTA(A:A))),SUMIF(INDIRECT(" A1:A"
& COUNTA(A:A)),C1,B:B))

However, this still doesn't work if more than three games were played.
Maybe someone else can have a go at it.

Good luck.
Paul


"PCLIVE" wrote in message
...
Ok.

With C1 as the team name you want the last two game totals, try this:

=IF(COUNTIF(A:A,C1)2,SUMIF(INDIRECT("A"&(MATCH(C1 ,A:A,0)+1)&":A"&COUNTA(A:A)),C1,INDIRECT("B"&(MATC H(C1,A:A,0)+1)&":B"&COUNTA(A:A))),SUMIF(INDIRECT(" A1:A"
& COUNTA(A:A)),C1))

HTH,
Paul


wrote in message
ups.com...
I'm sorry for being so confusing here. Rather than responding to your
message, let me just restate my problem.

My spreadsheet contains the following 2 columns [Animal} and [Points},
with the following data that will be expanded. Suppose each animal
plays a game, so in the following chart 9 games have been played. In
the first game, Monkey scored 10 points; in the next game Gorilla
scosred 20 point.

[Animal] [Points}
Monkey 10
Gorilla 20
Dog 30
Monkey 15
Dog 20
Cat 10
Monkey 30
Gorilla 20
Gorilla 30

I am looking to find out how many points Gorilla scored in the last 2
games that Gorilla played. In this case, the answer would be 50. If
the variable was Monkey, instead of Gorilla, the answer would be 45.

I need a formula that finds the last 2 scores for the animal I am
analyzing.

Thanks again.

PCLIVE (RemoveThis) wrote:
I'm not sure the example you've given is correct. If you total the
numbers
in column B for "abc" from column A, then the total should be 7. I'm
guessing this is what you want. If 7 is the correct answer, then maybe:

=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A"&C OUNTA(A:A)),B:B)

For the second to last entry in column A:

=SUMIF(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A"&C OUNTA(A:A)-1),B:B)


HTH,
Paul

wrote in message
ups.com...
Suppose you have 2 columns of data. Data gets added to the columns
frequently.

A B
xyz 4
abc 3
jkl 5
xyz 2
abc 1
def 4
abc 3

I need to calculate the total number in column B for the last 2
entries. Thus, if looking for "abc", the answer would be 4 (3+1),
since the abc got a 3 the last entry and 1 for the previous entry.
Using the same formula for xyz would return 6 (2 +4).

Any ideas on how I can accomplish this?

Hello:
Here is a VBA solution. If necessary, see David McRitchie's site on "getting
started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Function myTotal(myWhat)
Application.Volatile
myLast = Cells(Rows.Count, "A").End(xlUp).Row
myTotal = 0
MyCount = 0
For j = myLast To 1 Step -1
If Cells(j, "A").Value = myWhat Then
myTotal = myTotal + Cells(j, "B").Value
MyCount = MyCount + 1
End If
If MyCount = 2 Then Exit For
Next j
If MyCount < 2 Then myTotal = myTotal & " (found " & MyCount & ")"
End Function

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
ups.com...
Suppose you have 2 columns of data. Data gets added to the columns
frequently.

A B
xyz 4
abc 3
jkl 5
xyz 2
abc 1
def 4
abc 3

I need to calculate the total number in column B for the last 2
entries. Thus, if looking for "abc", the answer would be 4 (3+1),
since the abc got a 3 the last entry and 1 for the previous entry.
Using the same formula for xyz would return 6 (2 +4).

Any ideas on how I can accomplish this?