# 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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### 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
15
Views
270
Replies
1
Views
133
Replies
0
Views
204
Replies
2
Views
131
Replies
1
Views
439

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,825
Messages
5,766,659
Members
425,367
Latest member
Boboka

### 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