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

SUBTOTAL with OFFSET

P

Pugazh

New Member
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)),"")
 
Pugazh said:
Please find attached.
Click to expand...
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
 
Please read link below.
https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/
Specifically...
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
Click to expand...

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.
 
Dear Chihiro,
There is no solution received so far the problem i reported (actual EXCEL WORKBOOK attached).
 
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?
 
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)),"")
 

Attachments

  • Tracker C03 Status_Update.xlsx
    211.9 KB · Views: 5
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?
 
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.
 
=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.
 
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)
 
Excellent. It works and Subtotal function too returns the correct value when filter is applied. This is solved now. Thank You very much.
 
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)
 
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. :)
 
You must log in or register to reply here.
Back
Top