Hi All,
I'm trying to come up with a way to combine index functionality (look up row/column) with sumif functionality. I want to look up a column, and then sumif everything in that column that matches my criteria.
I've managed to do the above with an array formula, but there are complicating factors.
1) The sumif target column contains duplicates. I've pored through Aladin's posts on this forum, and I have an idea of how to approach this, although I haven't pulled it off yet.
2) There are multiple criteria, and they all need to be summed. In this instance, I'm trying to sumif based on all of the GL codes in a category. I don't want to repeat the formula for every code.
Summary: I want to look up a column based on a criteria, and in that column, sum every cell that matches a range of other criteria, while ignoring duplicates (but still adding the first value in the duplicate sets). Aladin approached this by counting duplicates and dividing by the number of counts, which could work.
I saw someone mention this would be easy to do in VBA, and I'm comfortable enough with VBA to do it that way if I can get a good directional start.
Much appreciated.
I'm trying to come up with a way to combine index functionality (look up row/column) with sumif functionality. I want to look up a column, and then sumif everything in that column that matches my criteria.
I've managed to do the above with an array formula, but there are complicating factors.
1) The sumif target column contains duplicates. I've pored through Aladin's posts on this forum, and I have an idea of how to approach this, although I haven't pulled it off yet.
2) There are multiple criteria, and they all need to be summed. In this instance, I'm trying to sumif based on all of the GL codes in a category. I don't want to repeat the formula for every code.
Summary: I want to look up a column based on a criteria, and in that column, sum every cell that matches a range of other criteria, while ignoring duplicates (but still adding the first value in the duplicate sets). Aladin approached this by counting duplicates and dividing by the number of counts, which could work.
I saw someone mention this would be easy to do in VBA, and I'm comfortable enough with VBA to do it that way if I can get a good directional start.
Much appreciated.