Conditional Running Count

curtins4u

New Member
Joined
Jan 21, 2005
Messages
21
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 :oops:
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Feb 17, 2002
Messages
13,686
Darnit. I thought that was the equivalent of "and"?

Shows what I still have to learn. :oops:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,148,045
Messages
5,744,489
Members
423,880
Latest member
CRE_finance_guy

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top