Conditional Running Count

[curtins4u]

Hi,
I have a spreadsheet with a column of week numbers and a column of numbers representing how long a job took to complete in that week.
What I need to know is how many jobs were completed in less than 4 days in a given week.
I've tried the following function

=COUNTIF(D2:D4905, AND(F2:F4905=30, D2:D4905 <4))

where D2:D4905 contains the number of days that a job took to complete (the records to be counted)
F2:F4905 contains the week numbers

So what I thougt I was asking for here is how many jobs in week 30 took less than 4 days to complete, but all I get back is 0. There are actually 95 records to be counted.

Thanks in advance

Jude

 

[Von Pookie]

You can't use multiple criteria in a COUNTIF function, unfortunately.

Try this:

=SUMPRODUCT(--($D$2:$D$4906<=4)*--($F$2:$F$4906=30))

 

[Aladin Akyurek]

You can't use multiple criteria in a COUNTIF function, unfortunately.

Try this:

=SUMPRODUCT(--($D$2:$D$4906<=4)*--($F$2:$F$4906=30))

Try to avoid using:

*--

together. It's either

=SUMPRODUCT(--($D$2:$D$4906<=4),--($F$2:$F$4906=30))

or

=SUMPRODUCT(($D$2:$D$4906<=4)+0,($F$2:$F$4906=30)+0)

or

=SUMPRODUCT(($D$2:$D$4906<=4)*($F$2:$F$4906=30))

--, +0, and * are all coercers from logical/truth values to 1's and 0's.

In the latter formula, * does both coercion and multiplication. It's in the first two the comma that stands for multiplication in accordance with the native syntax of the SumProduct function.

BTW, it's probably better to exclude empty cells...

=SUMPRODUCT(--($D$2:$D$4906<=4),--ISNUMBER($D$2:$D$4906),--($F$2:$F$4906=30))

 

[Von Pookie]

Darnit. I thought that was the equivalent of "and"?

Shows what I still have to learn.

 

[Aladin Akyurek]

Darnit. I thought that was the equivalent of "and"?

...

The comma or star can be said to effect an AND per pairs/triples, etc. of cells w.r.t. the conditional terms though.

 

[curtins4u]

Thanks very much. I'll give them a try.

Jude ray: