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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)),"")
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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
Back
Top