• 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...

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

Search results

  1. P

    SUBTOTAL with OFFSET

    i am using Excel 2016. Yes this works fine. Thank you for teaching me.
  2. P

    SUBTOTAL with OFFSET

    Excellent. It works and Subtotal function too returns the correct value when filter is applied. This is solved now. Thank You very much.
  3. P

    SUBTOTAL with OFFSET

    =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.
  4. P

    SUBTOTAL with OFFSET

    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?
  5. P

    SUBTOTAL with OFFSET

    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...
  6. P

    SUBTOTAL with OFFSET

    Dear Chihiro, There is no solution received so far the problem i reported (actual EXCEL WORKBOOK attached).
  7. P

    SUBTOTAL with OFFSET

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

    SUBTOTAL with OFFSET

    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
  9. P

    CHECK TEXT IN DIFFERENT CELLS AND RETURN VALUE IN ANOTHER CELL

    Thank You very much it works.
  10. P

    SUBTOTAL with OFFSET

    Please find attached.
  11. P

    SUBTOTAL with OFFSET

    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...
  12. P

    CHECK TEXT IN DIFFERENT CELLS AND RETURN VALUE IN ANOTHER CELL

    Dear Bosco, Subtotal returning null value is solved by using formula with Sumproduct/subtotal/offset formula. can you help in solving - if there is no entry or value in cells A7:F7 it returns #NUM!. cell G7 should be blank. Formula in cell G7 is...
  13. P

    CHECK TEXT IN DIFFERENT CELLS AND RETURN VALUE IN ANOTHER CELL

    cell AZ3=SUBTOTAL(103,AZ8:AZ300) - returns value null. column AZ8:AZ300 has the following formula. =INDEX({"NP";"D2";"D3";"OK"},AGGREGATE(15,6,MATCH(AT8:AY8,{"NP";"D2";"D3";"OK"},0),1))
  14. P

    CHECK TEXT IN DIFFERENT CELLS AND RETURN VALUE IN ANOTHER CELL

    Can anyone help on this please?
  15. P

    32bit file to run in 64bit

    Dear CHihiro, Can you please help on this. Option Explicit Private Declare PtrSafe Function SHBrowseForFolder Lib "shell64" _ (lpbi As BROWSEINFO) As LongPtr Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell64" _ (ByVal pidList As LongPtr, ByVal lpBuffer As String) As...
  16. P

    32bit file to run in 64bit

    Dear Hui, after changing "Private Ptrsafe Declare Function FunctionName" i get this message (see attached file)
  17. P

    CHECK TEXT IN DIFFERENT CELLS AND RETURN VALUE IN ANOTHER CELL

    Dear Bosco, This work but does not work when working with filters. Means value should change when filter is applied or some rows are hidden.
  18. P

    32bit file to run in 64bit

    I get a error message when i run a .xlsm file when i open the file. some of the cells are showing errors. screen shot attached.
  19. P

    CHECK TEXT IN DIFFERENT CELLS AND RETURN VALUE IN ANOTHER CELL

    Dear Bosco, Sorry. Typo error. E2:E6 is the range and value should return 5. Sample file attached. Please suggest.
  20. P

    CHECK TEXT IN DIFFERENT CELLS AND RETURN VALUE IN ANOTHER CELL

    Dear Bosco, Thanks. This works but i am not able to do subtotal of all the values returned. Means subtotal(103,E2:E3) = is showing blank. it should return value as 5.
  21. P

    CHECK TEXT IN DIFFERENT CELLS AND RETURN VALUE IN ANOTHER CELL

    Excellent. This works. Thank You very much.
  22. P

    CHECK TEXT IN DIFFERENT CELLS AND RETURN VALUE IN ANOTHER CELL

    in the above sheet values in A2:D6 will be either (NP, D2, D3 or OK). How to get automatically with formula to return a text in say column E2. Check cells (A2:D2) Condition1 is if any of the cell value is "NP" the value to be returned in is "NP". Condition2 if no "NP" then if there is D2 it...
  23. P

    Subtotal with sumif

    Dear Mr.John, I moved the cells and columns to match it to my base file. Formula returns zero value. What went wrong in the formula? I attached here Book3.xls. Could you please help on this?
  24. P

    Subtotal with sumif

    Thank You. I got it for 2019 as well. Works well.
  25. P

    Subtotal with sumif

    Thank you. what is the formula to be used for 2019 (cell D3)?
Back
Top