Count within Date Range only if valid criteria

[dingo114]

Hi everyone,

In an employee list, I am trying to count all leavers in a month based on their position.

So far I have:
=SUMPRODUCT((P4:P719>='startdate')*(P4:P719<='enddate))

which counts all leavers and works like a charm... but I can't get it to count only the leavers with position "SM" which is stored in column T ...

can anyone help out here????

Many thanks!

 

[Special-K99]

=SUMPRODUCT(--(P4:P719>='startdate')*(P4:P719<='enddate)*(T4:T719="SM"))

 

[Gilliam]

Hi everyone,

In an employee list, I am trying to count all leavers in a month based on their position.

So far I have:
=SUMPRODUCT((P4:P719>='startdate')*(P4:P719<='enddate))

which counts all leavers and works like a charm... but I can't get it to count only the leavers with position "SM" which is stored in column T ...

can anyone help out here????

Many thanks!

how about this assuming cells in column T hold for example "SM" only.
=SUMPRODUCT(--(P4:P719>='startdate'),--(P4:P719<='enddate'),--(T4:T719="SM"))

whereas a cells in column T may hold for example "SM somethingsomething"
=SUMPRODUCT(--(P4:P719>='startdate'),--(P4:P719<='enddate'),--(T4:T719="SM*"))

 

[dingo114]

how about this assuming cells in column T hold for example "SM" only.
=SUMPRODUCT(--(P4:P719>='startdate'),--(P4:P719<='enddate'),--(T4:T719="SM"))

whereas a cells in column T may hold for example "SM somethingsomething"
=SUMPRODUCT(--(P4:P719>='startdate'),--(P4:P719<='enddate'),--(T4:T719="SM*"))

Hi Gilliam,

thanks for your quick reply.

I tried that but get a "Value" error.

 

[Aladin Akyurek]

What are your startdate and enddate values?

Care to post just 5 values from column T?

 

[dingo114]

What are your startdate and enddate values?

Care to post just 5 values from column T?

My start date would be 1/7/2014 and end date 31/7/2014

In column T there are 3 options:
ASM
SM
Casual

 

[Aladin Akyurek]

My start date would be 1/7/2014 and end date 31/7/2014

In column T there are 3 options:
ASM
SM
Casual

A4: 1-Jul-14

B4: 31-Jul-14

C4: SM

1.

=SUMPRODUCT(--(P4:P719>=A4),--(P4:P719<=B4),--(T4:T719=C4))

2.

=COUNTIFS(P4:P719,">="&A4,P4:P719,"<="&B4,T4:T719,C4)

The latter is faster than the former. The count is based on SM, not on SM* or on *SM.

Do you want to count in both ASM and SM occurrences in the data?

 

[dingo114]

Hi Aladin,

I just came back from annual leave and tried it... it works perfect! thank you!

I do want to count both but in separate cells so I just created multiple formulas based on your feedback

thanks everyone!

 

[Aladin Akyurek]

Hi Aladin,

I just came back from annual leave and tried it... it works perfect! thank you!

I do want to count both but in separate cells so I just created multiple formulas based on your feedback

thanks everyone!

You are welcome. Thanks for providing feedback.