After sorting the formula has the incorrect cell

T

tiq

I am using the following
=SUMIF(Taken!A$1:A$1999,Accrual!C6,Taken!D$1:D$1999). When I do a sort on
the data the Accrual!C6 remains cell C6 but it should be C10 because I added
information and then sorted and the formula is now on line 10.

Can anybody help??
 
H

Herbert Seidenberg

Solution #1
Place the formula of C6 into the SUMIF() formula.
If, for example, C6 contains
=E1*F1 then the modified formula becomes:
=SUMIF(A1:A1999,E1*F1,D1:D1999)
Solution #2
Give C6 a name, say Tag, and type it into B6.
Select B6:C6 and
Insert > Name > Create > Left column
The modified formula becomes:
=SUMIF(A1:A1999,Tag,D1:D1999)
After sorting column B and C together, Tag will be in another row,
but adjacent to the formula =E1*F1.
Rename the formula as above.
Solution #3
If you want to get away from renaming Tag after each sort, do this:
Insert > Name > Define > Names in workbook > Tag
Refers To: =VLOOKUP("Tag",B1:C1999,2,0)
The SUMIF() formula stays the same as in #2
 
Top