=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.
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?
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...
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
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...
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...
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))
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...
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.
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...
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?