Need help with formula

[pierce.andy]

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?

 

[PCLIVE]

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"&COUNTA(A:A)),B:B)

For the second to last entry in column A:

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


HTH,
Paul

 

[pierce.andy]

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.

 

[PCLIVE]

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

 

[pierce.andy]

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]

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"&(MATCH(C1,A:A,0)+1)&":B"&COUNTA(A:A))),SUMIF(INDIRECT("A1:A"
& COUNTA(A:A)),C1))

HTH,
Paul

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.

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"&COUNTA(A:A)),B:B)

For the second to last entry in column A:

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


HTH,
Paul

 

[PCLIVE]

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"&(MATCH(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

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"&(MATCH(C1,A:A,0)+1)&":B"&COUNTA(A:A))),SUMIF(INDIRECT("A1:A"
& COUNTA(A:A)),C1))

HTH,
Paul

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.

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"&COUNTA(A:A)),B:B)

For the second to last entry in column A:

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


HTH,
Paul

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?

 

[pierce.andy]

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.

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"&(MATCH(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

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"&(MATCH(C1,A:A,0)+1)&":B"&COUNTA(A:A))),SUMIF(INDIRECT("A1:A"
& COUNTA(A:A)),C1))

HTH,
Paul

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"&COUNTA(A:A)),B:B)

For the second to last entry in column A:

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


HTH,
Paul

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?

 

[Bernard Liengme]

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

 

[PCLIVE]

Hi Andy,

Without using VBA, I think I found a way to do what you want. However, this
method will need to use a helper column.
Using column K in my example,

In K1 enter:
=IF(A1="","",IF(A1=$C$1,1,0))

Then in K2, enter:
=IF(A2="","",IF(A2=$C$1,1,0)+K1)

Copy K2 down as far as needed.

Column K is your helper column.
Now you can use this formula (below) to get your results for the last two
games played with C1 being the team name you want the results for.

=IF(COUNTIF(A:A,C1)<2,SUMIF(INDIRECT("A1:A" &
COUNTA(A:A)),C1,INDIRECT("B1:B" &
COUNTA(A:A))),INDIRECT("B"&MATCH(COUNTIF(A:A,C1),INDIRECT("K1:K"
&COUNTA(A:A)),0))+INDIRECT("B"&MATCH(COUNTIF(A:A,C1)-1,INDIRECT("K1:K" &
COUNTA(A:A)),0)))

HTH,
Paul

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.

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"&(MATCH(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

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"&(MATCH(C1,A:A,0)+1)&":B"&COUNTA(A:A))),SUMIF(INDIRECT("A1:A"
& COUNTA(A:A)),C1))

HTH,
Paul


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"&COUNTA(A:A)),B:B)

For the second to last entry in column A:

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


HTH,
Paul

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?

 

[pierce.andy]

Thanks Guys,

I used the VBA method and it worked perfectly, so I'll go with that one
for now. I have quite a bit of VBA experience in Word, so this isn't a
problem and it gives me a good excuse to learn VBA for Excel. I really
appreciate both of your help.

Andy

Hi Andy,

Without using VBA, I think I found a way to do what you want. However, this
method will need to use a helper column.
Using column K in my example,

In K1 enter:
=IF(A1="","",IF(A1=$C$1,1,0))

Then in K2, enter:
=IF(A2="","",IF(A2=$C$1,1,0)+K1)

Copy K2 down as far as needed.

Column K is your helper column.
Now you can use this formula (below) to get your results for the last two
games played with C1 being the team name you want the results for.

=IF(COUNTIF(A:A,C1)<2,SUMIF(INDIRECT("A1:A" &
COUNTA(A:A)),C1,INDIRECT("B1:B" &
COUNTA(A:A))),INDIRECT("B"&MATCH(COUNTIF(A:A,C1),INDIRECT("K1:K"
&COUNTA(A:A)),0))+INDIRECT("B"&MATCH(COUNTIF(A:A,C1)-1,INDIRECT("K1:K" &
COUNTA(A:A)),0)))

HTH,
Paul

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.

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"&(MATCH(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


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"&(MATCH(C1,A:A,0)+1)&":B"&COUNTA(A:A))),SUMIF(INDIRECT("A1:A"
& COUNTA(A:A)),C1))

HTH,
Paul


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"&COUNTA(A:A)),B:B)

For the second to last entry in column A:

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


HTH,
Paul

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?