Countif Problem

rmtimmah

Board Regular
Joined
May 14, 2007
Messages
153
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
Joined
Feb 20, 2005
Messages
578
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
 

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
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
Joined
May 14, 2007
Messages
153
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
Joined
Aug 12, 2005
Messages
976
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.

EDIT: I made edits above.
 

rmtimmah

Board Regular
Joined
May 14, 2007
Messages
153
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
Joined
Aug 12, 2005
Messages
976
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
Joined
May 14, 2007
Messages
153
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?
 

Forum statistics

Threads
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.
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