How to do COUNTIF with SUBTOTAL

[ZOZOZIAD]

Hi

in one column i have the values 1 or 2

to get the count for both i use =SUBTOTAL(2,D2:D158)

how can i have count for the value 2 only

[AliGW]

Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

[kersplash]

If you are using a pivot table it you should just apply the required filter.

[AliGW]

It should not have taken two and a half hours to get you to come up with a meaningful thread title. However, you have now done so and may receive help.

I have noted your comment (now deleted) about not caring about thread titles: please be advised that further infringements of our rules will be treated seriously and may result in a temporary or permanent ban from these forums.

Please make sure that you have read the rules before posting again: https://www.excelforum.com/forum-rul...rum-rules.html

[ZOZOZIAD]

You said that all previous clear and deleted , after posting i don't know from where this shown-up .

Also i read your discussions and you are expert and you know what i need for this formula ,

Why you don't want to provide me the solution???????? .

[AliGW]

Someone who can help you will provide you with a solution, hopefully, but you need to be patient. If that person were me, then I would have done so just as soon as you complied with the forum rules. You are batting on a very sticky wicket here: your attitude will not be impressing those who can help, making them less likely to bother dealing with you.

Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

From the forum rules (link in the main menu above):

• If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.

[ZOZOZIAD]

Successfully created .

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2,ROW(D2:D159)-MIN(ROW(D2:D159)),)),0+(D2:D159=1))