-2

Is there an easy way to modify the following formula to get it works on two letters columns? Thanks

=COUNTIFS(INDEX(A:Z, 2, CODE(UPPER(B5886))-64):INDEX(A:Z, MATCH("zzz", INDEX(A:Z, , CODE(UPPER(B5886))-64)), CODE(UPPER(B5886))-64), "-",
      INDEX(A:Z, 3, CODE(UPPER(B5886))-64):INDEX(A:Z, MATCH("zzz", INDEX(A:Z, , CODE(UPPER(B5886))-64))+1, CODE(UPPER(B5886))-64), "-")
Camberra CacaitA
  • 21
  • 8
  • Continuation of [Search for two consecutive rows with same data in Excel](https://stackoverflow.com/questions/45600066/search-for-two-consecutive-rows-with-same-data-in-excel) with no original effort towards a resolution shown. –  Aug 13 '17 at 17:34
  • Recommended reading: [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers) –  Aug 13 '17 at 17:40
  • @Jeeped I don't understand your meaning completely but I wont get response on the other post. – Camberra CacaitA Aug 13 '17 at 17:50
  • Function `OFFSET` is probably useful for you. – sancho.s ReinstateMonicaCellio Aug 13 '17 at 18:08
  • Without having your data, it is not easy to kow what you are trying to do. But `=SUBSTITUTE(ADDRESS(1,COLUMN(XES8),4),1,"")` can help you find the column letter (just change that `XES8` to the cell you are looking for). Then you can use `INDIRECT` to apply the concatenate address. – ian0411 Sep 01 '17 at 18:05

1 Answers1

0

The solution proposed here is not a modification of the formula you showed, but I think it produces the result you are looking for (so it is technically "an easy way to modify the formula", by replacing it).

Try the answer posted here giving two options based on SUMPRODUCT/INDIRECT or SUMPRODUCT/OFFSET, which should work flawlessly also for columns having two-letter names.

sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185