Replacing a COUNTIFS statement for excel 2003

[tommypayne]

Hi,

I'm looking to replace a COUNTIFS statement to enable the use of a workbook in Excel 2003.

The speadsheet is for a project that im running. The first criteria looks up the initials of someones name against a task and then the second criteria looks at if they have completed the task to work out which tasks are still outstanding.

=COUNTIFS('CONTRACTUAL TASKS'!$C$5:$C$24,C3,'CONTRACTUAL TASKS'!$D$5:$D$24,"NO")

Any help would be greatly appreciated. Please let me know if this is unclear.

Many thanks

Tommy

 

[VoG]

Try

=SUMPRODUCT(--('CONTRACTUAL TASKS'!$C$5:$C$24=C3),--('CONTRACTUAL TASKS'!$D$5:$D$24="NO"))

 

[Aladin Akyurek]

Hi,

I'm looking to replace a COUNTIFS statement to enable the use of a workbook in Excel 2003.

The speadsheet is for a project that im running. The first criteria looks up the initials of someones name against a task and then the second criteria looks at if they have completed the task to work out which tasks are still outstanding.

=COUNTIFS('CONTRACTUAL TASKS'!$C$5:$C$24,C3,'CONTRACTUAL TASKS'!$D$5:$D$24,"NO")

Any help would be greatly appreciated. Please let me know if this is unclear.

Many thanks

Tommy

Try...

Either, just enter:

=SUMPRODUCT(--('CONTRACTUAL TASKS'!$C$5:$C$24=C3),--('CONTRACTUAL TASKS'!$D$5:$D$24="NO"))

Or, control+shift+enter, not just enter:

=SUM(IF('CONTRACTUAL TASKS'!$C$5:$C$24=C3,IF('CONTRACTUAL TASKS'!$D$5:$D$24="NO",1)))

 

[tommypayne]

Many thanks for the rapid response.

=SUMPRODUCT(--('CONTRACTUAL TASKS'!$C$5:$C$24=C3),--('CONTRACTUAL TASKS'!$D$5:$D$24="NO"))

Worked perfectly!

All the best and have a nice weekend

 

[Zam]

My apologies for my ignorance... but what does the "--" operator do?

Thank you!

 

[VoG]

-- coerces an array of False/True values to 0/1 values that SUMPRODUCT can evaluate.

 

[Gerald Higgins]

It "co-erces" the results of the Array formula to be represented as 0s and 1s instead of FALSE and TRUE.

 

[Zam]

Thanks a lot! Appreciate the response!