Sumifs formula with variable column

[Greyangel1533]

How do I replace the the "AM:AM" in this sumifs formula =sumifs(Hrly!AM:AM with XLOOKUP(A3,HRLYTBL[Detail],HRLYTBL[Jul])? The HRLYTBL has cells that determine which column to sum based on a particular cell value.

[Gregb11]

Your profile says version 16. Does that mean Version 2016?
Please attach a sample file (please read the yellow banner at the top of the page).

[Greyangel1533]

I am sorry I have office 365

[Gregb11]

Replace what SUMIFS formula. I don't see one in the file to replace.

I'm guessing here, but are you looking for a formula on "Sheet2" to use what's in cell A2 to get the columns to look at?

In B2 I have a formula:
=XLOOKUP(A2,HRLYTBL[Detail],HRLYTBL[Jul],"Not found",0)
and then in B4, the formula is:
=SUMIFS(INDIRECT("Hrly!"&B2),Hrly!A:A,"F241")

I know this isn't what you're looking for exactly, but maybe this can help get you started.

See attached.

[Greyangel1533]

Replace what SUMIFS formula. I don't see one in the file to replace.

I'm guessing here, but are you looking for a formula on "Sheet2" to use what's in cell A2 to get the columns to look at?

In B2 I have a formula:
=XLOOKUP(A2,HRLYTBL[Detail],HRLYTBL[Jul],"Not found",0)
and then in B4, the formula is:
=SUMIFS(INDIRECT("Hrly!"&B2),Hrly!A:A,"F241")

I know this isn't what you're looking for exactly, but maybe this can help get you started.

See attached.

Thank you for your help. Here I attached the summary sheet that this sheet builds up to I only shared 1 of the hundreds of rows on this file. Based on the DTL I need it to go to Formulas table to grab column and then go to a specific spreadsheet some of which are confidential which is why I can't post them.

[Greyangel1533]

Just want to see if anyone else has any other ideas. I think I have to use the index and match function somehow but I am unsure how to do this.

[nick.williams]

We don't need to see your actual data, but it's hard to advise without knowing what we are dealing with. You can change names on sheets, anonymise any data, and delete most of the rows, we just need to see the structure.

When you say you need to formula to go to a specific spreadsheet, it this one specfic spreadsheet, or is the name of this being referenced somewhere as well?

Also, with your SUMIFS formula, what do you want to actually sum?

If you are using references stored in cells then you will likely need to use INDIRECT. This can make the sheet run slowly if you have lots of them.

[Greyangel1533]

Indirect is producing a #spill: =+SUMIF(Hrly!$D:$D,'Data-a'!D41,INDIRECT("'HRLY'!",INDEX(HRLYTBL[Jul],0,MATCH('Data-a'!D41,HRLYTBL[Detail],0))))

[nick.williams]

Please post a sample workbook showing the error. It looks like you have the syntax wrong, but I can't tell what it needs to be as I don't know what you are trying to do.

[Greyangel1533]

Here is the spreadsheet.

[nick.williams]

Ok so you had this:

=+SUMIF(Hrly!$D:$D,'Data-a'!D41,INDIRECT("'HRLY'!",INDEX(HRLYTBL[Jul],0,MATCH('Data-a'!D41,HRLYTBL[Detail],0))))

I've highlighted a few bits in red.

The + at the start is redundant, though it doesn't cause any errors.

The , should be & as the INDIRECT function just wants a string.

The 0, should be removed, this is the row reference in the INDEX function, putting 0 gives the whole row and then the next argument (optional) is column, so what you were returning was the column reference that you matched with the row reference you looked up.

If you remove that it works, and the formula returns 0 because nothign in your example sheet has a value of 622 in column D of Hrly. I guess you want to change something else, but it's really not worth me guessing the details and you seem very reluctant to provide a meaningful template (even this one is different from the formula you said didn't work, and didn't have a SPILL error anywhere). So in summary a working formula in W3 of your sample sheet is:

=+SUMIF(Hrly!$D:$D,'Data-a'!D3,INDIRECT("'HRLY'!"&INDEX(HRLYTBL[Jul],MATCH('Data-a'!D3,HRLYTBL[Detail],0))))

[nick.williams]

...which looking back is really very simlar to the answer you were given in post 4 by Greg, albeit that uses XLOOKUP instead of INDEX MATCH.

[Greyangel1533]

That works Nick thank you. Sorry my previous spreadsheet was so bad. Here is my final formula and the spreadsheet. I realized I had a couple of cell reference errors previously. =SUMIF(Hrly!$D:$D,B3,INDIRECT("'HRLY'!"&INDEX(HRLYTBL[Jul],MATCH('Data-a'!D3,HRLYTBL[Detail],0)))).

[Greyangel1533]

Formula goes in Column W