Contact Me 28

As well as through comments to the various blogposts, I would be more than happy to answer any questions, solve any problems, etc. from readers looking for a formula-based solution to a particular situation they may be faced with: just drop me an e-mail at XORLX99@GMAIL.COM

Of course, any other comments, suggestions for improvement, etc. are also welcome. And if anyone has a particular topic in mind which they’d wish to see a post on, please let me know and I’ll do my best to accommodate that wish.


  1. hi, I love the ideas & the innovation you have here on your blog. My aged eyes however can’t keep up with the text being almost the same [color/shade/ hue/whatever its called] as the background. Could you please make it easier for your older readers?

  2. Hi Neil and welcome!

    Thanks for your kind words. I also appreciate your comment re the layout and it is something I am working on right now. Hopefully I will have a new format at some point in the not-too-distant future.


  3. wwwww XOR LX you are absolutly the best exceller that I know

    I saw your formula in the forum and in this web site… absolutly ADVANCE you have a solution for every thing….

    I don’ t know if you are an Excel MVP in case not you deserve it a soon as possible

  4. Hello:

    I’ve heard of the ROW(INDEX:INDEX) formula but can’t find any reference to it’s use in practice.

    Can you please enlighten me?



  5. Hi Vic, and thanks for your query.

    Personally I use that construction almost exclusively in place of the (sadly more common, yet volatile) equivalent ROW/INDIRECT set-up.

    In fact, I used it in my most recent post:

    What precisely is it about this construction that you’re having difficulty understanding?

    To take a simple example, in order to generate a dynamic array of integers from 1 up to the value in cell A1, we could use the aforementioned volatile ROW/INDIRECT set-up, viz:


    though the (almost) non-volatile ROW/INDEX set-up is preferable, in my opinion:


    the point being that, in this usage (and assuming that A1 contains, for example, 10):




    resolve to the cell references:




    respectively, and not to the actual cell contents of those references (INDEX is one of the very few functions that has this wonderful dual property of being able to return both a cell reference and a cell content, depending on how the expression is constructed).

    Which means that:


    resolves to:




    as required.

    Hope that helps.

  6. Hello XOR LX,

    Many thanks for your quick reply.

    With that, plus a read-through of Molecular Weights and another look at the INDEX syntax, all is now clear.

    Congratulations on an excellent site.



  7. hi XORLX…hreat site I found. Need to learn a lot more to appreciate your advanced level. I have emailed you a quick question. Would greatly appreictae if you could help

  8. How to register on this site/blog?? Can i direct to another forum for my excel VBA question?

  9. Hi there,

    How to you take this formula for multiple criteria & get it to work with filtered cells?

    =COUNTIFS(B2:B14,"Male",C2:C14,"Sea lion")
  10. @Rich

    In that case you would need to switch to SUMPRODUCT:

    =SUMPRODUCT(0+SUBTOTAL(3,OFFSET(B2,ROW(B2:B14)-MIN(ROW(B2:B14)),)),0+(B2:B14="Male"),0+(C2:C14="Sea lion"))

    Hope that helps!


  11. I’m trying to expand on it, what if I wanted it look at a range of time?

  12. Sorry, the previous post didn’t post correctly.Let’s try again..

    I’m trying to expand on it. What if I want it to look at a range of time?

  13. =SUMPRODUCT(0+SUBTOTAL(3,OFFSET(S2,ROW(S2:S40675)-MIN(ROW(S2:S40675)),)),0+(S2:S40675>="00:00"),0+(S2:S40675<="00:59"))


  14. There’s nothing syntactically wrong with your formula. Why aren’t the results you are getting what you would expect? Are the values in that range formatted as proper time values?

    Perhaps you can share a link to the actual workbook if you still can’t get it to work.


  15. Hi XOR, i do not even know your real name 🙂 i was very much interested in discussions of Advance formula challenges, why did it stop? i know everyone is busy on daily job what pays the bills, i was wondering, if you have any intention to post new challenges.

  16. Hi Jamil,

    Thanks for your kind words – I’m glad that you enjoyed some of the work in those challenges. As to when the next one will be, what can I say? You’re right that I’ve hit a spot right now in which I’m struggling to find the time to create new posts, including the Advanced and Shortest Formula Challenges.

    But all I will say is that I’m still alive and well, and have absolutely no intention of letting this site go to ruin. As soon as I can find a bit of time then I’ll be sure to have some new posts (and challenges) coming your way.


  17. Thanks XOR, there is a lot of useful best Excel stuff in your website which cannot be found in any book. if i was you, i would have started looking for a editor and publisher to create a book from contents of your blog/site.

  18. @Jamil Many thanks. You’re not the first person to have suggested that to me. Perhaps in light of your further encouragement in that respect I may decide to follow this advice.


  19. My own book is forever stuck at 95% complete. Can’t wait to finish it, and at the same time wonder when I’ll ever find the time.

  20. Hi,

    I have the same problem in Google sheets and I tried the semicolon solution but it did not work. Do you have an idea how can I solve the same problem in Google sheets?

    Thank you.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.