• 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

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

New Member
Please find attached.
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
 

Chihiro

Excel Ninja
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.
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.
 

Pugazh

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

Chihiro

Excel Ninja
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?
 

Pugazh

New Member
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

Pugazh

New Member
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?
 

Chihiro

Excel Ninja
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.
 

Pugazh

New Member
=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.
 

Chihiro

Excel Ninja
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

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

Chihiro

Excel Ninja
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)
 

Chihiro

Excel Ninja
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. :)
 
Top