Can I use index as my range in a formula.

[helpneeded2]

I have 20 columns (AU:BV) in my spreadsheet. These columns contain a number of transactions for a client, and the header contains the name of a location. There are about 150,000 rows of clients.

In a separate sheet, I have a list of the location names. For each location, I want a column that will total all the client transactions at that location, if they client meets certain conditions (i.e.: the value in column AM = "Yes", and the value in AO = "H" or "P".

I am not very experienced at Excel, so I just can't figure out a way to do this.

I believe it would require a sumif, such as =sumif(range,AM:AM,"Yes",AO,{"H","P"}) ?

However since the range (the column to be summed) is going to differ between location names, I don't know how to link the 2 together, aside from making a 2 column table for the location names, and then a text value for the range ("AU"), and then lookup that value and make that the range via an indirect statement -- but I'm sure there is a better way?

 

[jasonb75]

You could use something like

=sum(sumif(index(AU:BV,0,match(location,AU1:BV1,0)),AM:AM,"Yes",AO:AO,{"H","P"}))

 

[helpneeded2]

You could use something like

=sum(sumif(index(AU:BV,0,match(location,AU1:BV1,0)),AM:AM,"Yes",AO:AO,{"H","P"}))

That works perfectly (after changing the sumif to a sumifs).

Thank you greatly for your time in replying, and for being able to decipher my narrative of the issue.

 

[jasonb75]

after changing the sumif to a sumifs).

Oops, i forgot to change that after i copied your theory formula from post 1 ro cut down on the typing.