# Countif Problem

#### rmtimmah

##### Board Regular
Hi, I have a sheet that contains dates going back a few years. I am trying to use the countif function to count the different sites in column B according to date. eg I want to be able to find out how many jobs for PHO1 were created between todays date and 7 days ago, this can go into column C, In column D I need to find out how many jobs were created by PHO1 between 7 and 14 days ago etc etc.
Book2.xls
ABCD
126/07/2007PHO1
226/07/2007PHO1
326/07/2007ISA1
426/07/2007CC01
526/07/2007CC01
626/07/2007CC01
727/07/2007CC01
Sheet1

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Glove_Man

##### Well-known Member
I sugegst SUMPRODUCT rather than countif. Something like

In cell C1 and assuming that the data goes down to row 100:

=SUMPRODUCT(--(\$B\$1:\$B\$100 = "PHO1"),--(\$A\$1:\$A\$100 >=today()-7))

Philby

#### rmtimmah

##### Board Regular
lovely works a treat. Can I ask what the -- does?

#### ExcelChampion

##### Well-known Member
The double negative coerces boolean values into 1 or 0 by applying a mathematical operation to the condition (ie, multiplying by a -(-1) or --. The same result with +0, *1, etc.)

As an experiment, in a cell, type =--(TRUE)

...then try it with FALSE.

Then try it with +0: =TRUE+0

#### rmtimmah

##### Board Regular
OK thanks.

On another note the above formula works great when you name a range of cells but my actual sheet contains about 35000 rows and is continually growing. When I change the absolute values to say A:A it gives me a #NUM error. Even if I use =if(B:B>0,SUMPRODUCT(--(B:B = "PHO1"),--(A:A >=today()-7)),"") i get nothing.

#### ExcelChampion

##### Well-known Member
One of the things about array formulas is that you can't have whole column ranges.

You will need to create a dynamic range.

Replace A:A with:

A2:INDEX(A:A,MATCH(REPT("z",255),B:B))

Replace B:B with:

B2:INDEX(B:B,MATCH(REPT("z",255),B:B))
You can leave this part as is:
=if(B:B...

You only need to change what is in the SUMPRODUCT part of the formula.

#### rmtimmah

##### Board Regular
I am still getting nothing. I do have some blank cells in Column B so my If statement must be wrong somehow. This is my formula

=IF(B:B>0,SUMPRODUCT(--(B2:INDEX(B:B,MATCH(REPT("z",255),B:B))="PHO1"),--(A2:INDEX(A:A,MATCH(REPT("z",255),B:B))>=TODAY()-7)),"")

#### ExcelChampion

##### Well-known Member
Actually, I didn't think of it but what does IF(B:B>0 supposed to be doing?

Column B I take contains text values...so why are you saying >0?

You should be able to get rid of the IF part and work as intended.

#### rmtimmah

##### Board Regular
Sweet. Works now. Cheers

#### rmtimmah

##### Board Regular
OK now I have that working, part 2 of my original question was In column D I need to find out how many jobs were created by PHO1 between 7 and 14 days ago. How can that be done?

Replies
4
Views
265
Replies
14
Views
428
Replies
1
Views
2K
Replies
3
Views
450
Replies
2
Views
182

1,181,416
Messages
5,929,790
Members
436,693
Latest member
BroTr

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