VLOOKUP

[Guest]

I have a column that contains the text "Total amount" multiple timesin
different rows. I need to use totals in these specific rows at different
times in a calculation. The list length varies every day as new rows are
added and deleted. The number of rows containing the text "Total amount" will
always remain the same. How can I instruct excel to find the applicable row
amongst all the rows that have the same text in the same column and that
varies on a daily basis in lenght?

 

[Guest]

One way is to use a helper col, then INDEX/MATCH on it ..

Assuming source data in cols A to J from row2 down
with "Total amount" phrases occurring in col A

Use an adjacent empty col to the right, say col K

In K2:
=IF(A2="Total amount",A2&COUNTIF($A$2:A2,A2),"")
Copy K2 down to cover the extent of expected source data. Col K will create
the additional identifiers: Total amount1, Total amount2, etc which could
then be used in an INDEX/MATCH.

For example, if you then list in N2 down:

Total amount1
Total amount2
Total amount3
etc

and you want to extract the corresponding col B values in the source
just place in O2: =INDEX(B:B,MATCH($N2,$K:$K,0))
and copy O2 down. As-is you could also then fill across to return corresp
values from col C, D, E ...

 

[Guest]

Hi,

Let's say your workseet is like below:

A B

1 Total amount 180.69
..
..
20 Total amount 582.69
..
..
100 Total amount 1589.25
..
..

and you want to sum all figurs in column B that has "Total amount" in
approprate Cell in column A

so you have to enter this formula:

=SUMIF(A1:A100,"Total amount",B1:B100)

A100 and B100 are for this example and they can change to for example A1000
and B1000

Thanks,