Thanks to the forum...Apparently all I had to do was post here to FINALLY figure it out myself....(I think)
Hello,
I am trying to write a formula that will have excel determine the sumrange to use in a sumif formula based on the text value in a given cell. Can anyone help please?
Eg. SUMIF(SAV838_ISCAT,$A36,sumrange)
Where:
SAV838_ISCAT is a named range of $A$4:$A$999 on reference tab Savings838
$A36 contains an account shortcode (eg. “DIN”)
Cell H1 contains the text “Jan ‘10”
I am struggling to figure out how to get excel to look to the same tab of the name range above (Savings838) and based on the sumif criteria, sum the correct column (Jan ’10); of course tab Savings838 also has a column “Jan ‘10’ with the values I want summed. I do not want to have to tell it which column as I would have to change it every month. I want it to look to cell H1 and match the month and sum if the name range equals DIN.
Cell H1 will change for each month (eg. Feb would be N1)
I’ve tried offset and match within the sumif and named ranges but can’t figure out how to do it properly so it will work.
SAV838DATA is my full data range on tab Savings838 (range $F$4:$Q:$999)
on tab Savings838, the months are found on row 1, colums F through Q
=SUMIF(SAV838_ISCAT,$A36,OFFSET(SAV838DATA,0,MATCH(H$1,savings838!$F$1:$Q$1,0)-1,999,1))
Can anyone please help?
Bookmarks