I successfully use :
=SUMIF($A40:$A97,B2,Z40:$Z97) to find the variable in B2 in column A and return the value from column Z.
I want to find a value from a column which varies depending on the title of the row.
i.e. Column Z has a title in row 5 as Balance c/f and a date in row 6 as 2017. The calcs in the next three columns produce another Balance c/f in column AD and the date is 2018 in row 6. So every 4 columns the calcs in Z are repeated.
I want to simply say find the variable in B2 in column A and return the value from a column when row 6 matches a variable which I will have in say cell f2 and in the case of column Z would be 2017. But if I put 2018 in cell f2 then the value in column AD would be returned ..does that make sense?
So I thought I could use:
=SUMIFS(Z40:Z97,$A40:$A97,B2,L6:AD6,f2) but that just returns #VALUE ! So am I missing something?
Secondly but not essential, how would I ensure that I choose only the column where BOTH row 5 = Balance c/f and row 6 matches the F2 criteria?
Many thanks
=SUMIF($A40:$A97,B2,Z40:$Z97) to find the variable in B2 in column A and return the value from column Z.
I want to find a value from a column which varies depending on the title of the row.
i.e. Column Z has a title in row 5 as Balance c/f and a date in row 6 as 2017. The calcs in the next three columns produce another Balance c/f in column AD and the date is 2018 in row 6. So every 4 columns the calcs in Z are repeated.
I want to simply say find the variable in B2 in column A and return the value from a column when row 6 matches a variable which I will have in say cell f2 and in the case of column Z would be 2017. But if I put 2018 in cell f2 then the value in column AD would be returned ..does that make sense?
So I thought I could use:
=SUMIFS(Z40:Z97,$A40:$A97,B2,L6:AD6,f2) but that just returns #VALUE ! So am I missing something?
Secondly but not essential, how would I ensure that I choose only the column where BOTH row 5 = Balance c/f and row 6 matches the F2 criteria?
Many thanks
