1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUBTOTAL with OFFSET

Discussion in 'Ask an Excel Question' started by Pugazh, Jan 18, 2019.

  1. Pugazh

    Pugazh New Member

    Messages:
    27
    Formula below does not return any value? What is wrong with this formula?
    =SUMPRODUCT(SUBTOTAL(103,OFFSET(AZ8:AZ488,ROW(AZ8:AZ488)-ROW(AZ8),0,1)),--(AZ8:AZ488<>""))

    Requirement is count all cells which has text or numbers and leave the blank cells from counting. Each cell in the range AZ8:AZ488 has the following formula
    =IF(COUNTA(AT9:AY9),INDEX({"NP";"D2";"D3";"OK"},AGGREGATE(15,6,MATCH(AT9:AY9,{"NP";"D2";"D3";"OK"},0),1)),"")
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,246
    Sample workbook please.
  3. Pugazh

    Pugazh New Member

    Messages:
    27
    Please find attached.

    Attached Files:

  4. Nebu

    Nebu Excel Ninja

    Messages:
    2,141
    Hi:

    May be this?

    =SUMPRODUCT(--(LEN(AZ8:AZ488)>0))

    Thanks
  5. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,150
  6. Pugazh

    Pugazh New Member

    Messages:
    27
    Dear Chihiro,
    As requested last week i attached actual work sheet. Did you find time to look at? Could you please check and advise the correct formula to be used to get the desired output. Thanks
  7. Pugazh

    Pugazh New Member

    Messages:
    27
    THis also does not work. I attached actual workbook last saturday. Could you please check and advise,
  8. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,246
    Please read link below.
    https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/
    Specifically...
    While it isn't prohibited to cross-post. I tend to skip thread where cross-post is reported and appropriate response hasn't been made.
  9. Pugazh

    Pugazh New Member

    Messages:
    27
    Dear Chihiro,
    There is no solution received so far the problem i reported (actual EXCEL WORKBOOK attached).
  10. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,246
    So, you must describe how you applied the solution provided and what exactly was "not working". Did it return error? did it return unexpected result? etc. What is the expected result?
  11. Pugazh

    Pugazh New Member

    Messages:
    27
    upload_2019-1-23_22-27-50.png

    Below given Formula in cell AZ3 does not return any value? Actual count value is 218.What is wrong with this formula?
    =SUMPRODUCT(SUBTOTAL(103,OFFSET(AZ8:AZ488,ROW(AZ8:AZ488)-ROW(AZ8),0,1)),--(AZ8:AZ488<>""))

    Requirement is to count all cells which has text or numbers and leave the blank cells from counting. Each cell in the range AZ8:AZ488 has the following formula
    =IF(COUNTA(AT9:AY9),INDEX({"NP";"D2";"D3";"OK"},AGGREGATE(15,6,MATCH(AT9:AY9,{"NP";"D2";"D3";"OK"},0),1)),"")

    Attached Files:

  12. Pugazh

    Pugazh New Member

    Messages:
    27
    This formula works for the total count. =SUMPRODUCT(COUNTIF($AZ8:$AZ488,{"OK";"D3";"D2";"NP"})).

    But, How to convert this formula to work with filters?
  13. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,246
    It's simple really. SUBTOTAL by default will ignore values calculated using other SUBTOTAL/AGGREGATE functions. Hence, SUBTOTAL part of the formula will always return array of zeros only. And formula will always evaluate to 0.

    You need to add helper column, and use that column for calculation purpose. Or change formula in AZ8:AZ280 range to not use Subtotal/Aggregate function.

    That's it.
  14. Pugazh

    Pugazh New Member

    Messages:
    27
    =IF(COUNTA(BQ10:BV10),INDEX({"NP";"D2";"D3";"OK"},AGGREGATE(15,6,MATCH(BQ10:BV10,{"NP";"D2";"D3";"OK"},0),1)),"")

    What could be the alternate formula that we can use without using AGGREGATE function.
  15. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,246
    Why the aversion to helper column? It will make it easier to manage workbook and can reduce overhead.

    At any rate following will do the same.
    =IF(COUNTA(AT8:AY8),INDEX({"NP";"D2";"D3";"OK"},MIN(IF(ISNUMBER(MATCH(AT8:AY8,{"NP";"D2";"D3";"OK"},0)),MATCH(AT8:AY8,{"NP";"D2";"D3";"OK"},0)))),"")

    Confirmed as array (CTRL + SHIFT + ENTER)
    Pugazh likes this.
  16. Pugazh

    Pugazh New Member

    Messages:
    27
    Excellent. It works and Subtotal function too returns the correct value when filter is applied. This is solved now. Thank You very much.
  17. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,246
    Oh, depending on the version of Excel you use. You can use shorter formula below.
    =IF(COUNTA(AT8:AY8),INDEX({"NP";"D2";"D3";"OK"},MIN(IFERROR(MATCH(AT8:AY8,{"NP";"D2";"D3";"OK"},0),5))),"")

    Confirmed as array (CTRL + SHIFT + ENTER)
    Pugazh likes this.
  18. Pugazh

    Pugazh New Member

    Messages:
    27
    i am using Excel 2016. Yes this works fine. Thank you for teaching me.
  19. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,246
    Oh, please update your MrExcel.Com thread with the answer provided. It's a common courtesy to extend, when you cross-post in different forums. :)

Share This Page