Counting number of cells in a range with dates over a certai

[alphazero]

I have a list of dates. I want to be able to count how many of those fields have dates that are 90 days or less from today's date.

Sorry if this has been covered before... I did try a search, but didn't find anthing.

Thanks!

 

[Aladin Akyurek]

On 2002-08-26 11:34, alphazero wrote:
Ok. THanks a lot.

I have another formula I am trying to get working:

=COUNTIF(AVERAGE(F23:F31), B5)

Basically, I want to get the average of a group of numbers... but only include the numbers associated with the value of "B5". I keep getting a message that I have an error... but can't see what is wrong with the above formula.

How do I know whether a number in F3:F31 is asociated with the value in B5? That is, Is there a range associated with F3:F31, for which B5 must hold?

 

[alphazero]

Yes. You're right.

B5 should be associated with the values B23:B31. If any values between those cells matches B5, then the value in the associated cells F23:F31 should be included in the Average. How do I change my formula to make this work?

 

[Aladin Akyurek]

On 2002-08-26 12:09, alphazero wrote:
Yes. You're right.

B5 should be associated with the values B23:B31. If any values between those cells matches B5, then the value in the associated cells F23:F31 should be included in the Average. How do I change my formula to make this work?

Either of:

[1]

=AVERAGE(IF(B23:B31=B5,F23:F31))

[2]

=SUMIF((B23:B31,B5,F23:F31)/MAX(1,SUMPRODUCT((B23:B31=B5)*(ISNUMBER(F23:F31))))

The formula in [1] must be entered by hitting control+shift+enter at the same, not just enter.

 

[alphazero]

You people are great! Thanks again. I had actually tried method #1 before, but didn't understand the ctrl+shift+enter combo. Working great, thanks!

 

[PaddyD]

you need to array enter a combination of average & if.

(see http://www.mrexcel.com/tip011.shtml for info on array formulas - a.k.a. CSE formulas).

The following gives an example: with data in a2:b5, find the average of the numbers in col a with an 'a' in col b. The formula is:

=AVERAGE(IF(B2:B5="a",A2:A5))

(which needs to be array entered - with control + shift + enter, enot just enter. excel will add the curly brackets).

Book4
	A	B	C	D
1	Nums	Ids	Aveof'a'
2	1	a	1.5
3	2	a
4	3	b
5	4	b
6
Sheet1

Hope this helps,
Paddy