# Conditional Running Count

#### curtins4u

##### New Member
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.

Jude

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Von Pookie

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

Try this:

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

##### MrExcel MVP
Von Pookie said:
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

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

Shows what I still have to learn.

##### MrExcel MVP
Von Pookie said:
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

##### New Member
Thanks very much. I'll give them a try.

Jude ray:

Replies
1
Views
93
Replies
2
Views
127
Replies
3
Views
80
Replies
1
Views
61
Replies
3
Views
144

1,181,621
Messages
5,930,997
Members
436,770
Latest member
Student_Engineer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back