SOLVED

# Count if date range using subtotal

Occasional Contributor

# Count if date range using subtotal

I am trying to add the subtotal function to be able to count only the filtered values. I want to count only the employees that have been hired in 2022. So, my table looks something like this:

Name Group Hiring Date

 John A 13/01/2022 Peter B 12/01/2022 Wesley B 07/03/2022 Jennifer B 18/12/2021

I was using this formula and it was working ok (where the output is equals to 3).

`=COUNTIF(C2:C5,">01/01/2022")`

However now my desired output in case I filter "Group B" for example will be 2.

I know that I should use subtotal but I couldn´t add the formula correctly. Can anyone help me with that?

Thanks a lot!

@Riny_van_Eekelen maybe you can help me out with that because you already helped with something similar a few months ago!

8 Replies

# Re: Count if date range using subtotal

@brunotavares Perhaps like this:

``=COUNTIFS(C2:C5,">01/01/2022",B2:B5,"B")``

# Re: Count if date range using subtotal

@Riny_van_Eekelen
It works for group "B" but the problem is that I have a lot of different groups and the idea will be for the person to choose what he/she needed it and not having to do it every time manually =(
That is why I was thinking that the subtotal may be helpful

# Re: Count if date range using subtotal

@brunotavares Then choose a cell where the user can enter A or B or C and link to that cell. See picture where B is entered in E3.

# Re: Count if date range using subtotal

Ok, thank you, this can a good alternative! But there is no way to do it using the subtotal function?

# Re: Count if date range using subtotal

@brunotavares Sorry, didn't get the subtotal issue. Can you upload a file/screenshot demonstrating what you are facing?

# Re: Count if date range using subtotal

@Riny_van_Eekelen Alright, I just attached the file with some comments and also a screenshot. The formula in yellow that I am using is

=COUNTIF(Table6[HIRING DATE],">01/01/2022")

best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Count if date range using subtotal

@brunotavares Why filter on GFK? Just enter the group in some other cell and use the formula I mentioned in my earlier post. Otherwise, add one more filter for the year 2022 only and then add a "Total Row" to count the number of row that fulfil the criterion.

See attached.

# Re: Count if date range using subtotal

It is hard to explain hahah but you gave me a good idea! Thanks a lot.