SubTotal

[MARKEGANDERSON]

Hello All-

I want to count all the "Yes", in a data range define as "CPR_Data", using the SubTotal function.

Can anyone help?

 

[Aladin Akyurek]

Thanks Aladin. it is now returning a value of 525

The formula is correct...

What is your date range where you run autofilter?

 

[MARKEGANDERSON]

Aladin - Each week has a different date and different number of candidates. This week's group Column E(date column) is 08/20/18.

This works :=SUBTOTAL(3,T1:T9812) to give me a count of my "Yes" on whatever auto filter I have on. But counts all 22 candidates(each cell in T column has a formula)

 

[Aladin Akyurek]

Aladin - Each week has a different date and different number of candidates. This week's group Column E(date column) is 08/20/18.

This works :=SUBTOTAL(3,T1:T9812) to give me a count of my "Yes" on whatever auto filter I have on. But counts all 22 candidates(each cell in T column has a formula)

Does the following behave differently?

=SUMPRODUCT(​

SUBTOTAL(2,OFFSET(E1:E9812,ROW(E1:E9812)-ROW(INDEX(E1:E9812,1)),0)),--(​

T1:T9812="yes"))​

<strike style="background-color: transparent; color: rgb(51, 51, 51); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px; word-wrap: break-word;"></strike>

​

 

[MARKEGANDERSON]

now it is 144

 

[Aladin Akyurek]

now it is 144

What is in E1?

What is in T1?

What is the date for which you filter?

What do the following return?

=COUNT(E1:E9812)

=COUNTIFS(T1:T9812,"yes")

=SUMPRODUCT(--(E1:E9812=X1),--(T1:T9812="yes") where X1 houses the date for which we have been filtering.​

<strike></strike>


​

​

<strike></strike>

 

[MARKEGANDERSON]

What is in E1? blank cell

What is in T1?blank cell

What is the date for which you filter?08/20/18

What do the following return?

=COUNT(E1:E9812)4564(Count all the candidates)

=COUNTIFS(T1:T9812,"yes")24 -(its adding all the "YES" within that range :T1:T9812)

=SUMPRODUCT(--(E1:E9812=X1),--(T1:T9812="yes") where X1 houses the date for which we have been filtering.

=SUMPRODUCT(--(E1:E9812=X1),--(T1:T9812="yes") This works if you set the value of the date you have it filtered by to X1

 

[Aladin Akyurek]

In E1 enter: DATE

In T1 enter: Yes?

Activate Autofilter on E1 & filter for the date 08/20/2018.

In U1 enter:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(E2:E9812,ROW(E2:E9812)-ROW(INDEX(E2:E9812,1)),0)),--(T2:T9812="yes"))

By the way, why do you autofilter at all if the following already deliver the count you want?

=SUMPRODUCT(--(E2:E9812=X1),--(T2:T9812="yes")

=COUNTIFS(E2:E9812,X1,T2:T9812,"yes")

where X1 = 08/20/2018
​

<strike></strike>


​

​

 

[MARKEGANDERSON]

Thanks Aladin - The goal was to show how many people needs CPR for that week, so in Column T, if the candidate job title required CPR, then "yes" would populate in column T. So the formula would show how many people needed to take the course. I thought a simple IF and subtotal combination would work.

Thanks again for all your help

 

[Aladin Akyurek]

Thanks Aladin - The goal was to show how many people needs CPR for that week, so in Column T, if the candidate job title required CPR, then "yes" would populate in column T. So the formula would show how many people needed to take the course. I thought a simple IF and subtotal combination would work.

Thanks again for all your help

But... What does the job?

This

=SUMPRODUCT(

SUBTOTAL(2,OFFSET(E2:E9812,ROW(E2:E9812)-ROW(INDEX(E2:E9812,1)),0)),--(​

T2:T9812="yes"))

or this

=COUNTIFS(​

E2:E9812,"2018-08-20",T2:T9812,"yes")

or none?
​

​

 

[MARKEGANDERSON]

Thanks Aladin. This one works =COUNTIFS(
E2:E9812,"2018-08-20",T2:T9812,"yes")!! Thanks again for all you do