Formula Help 1

[arvarr]

Hi
Hoping someone can help.
I'm trying to do a counta for items in a column.

Column A contains dates 2010-07-01 all the way up to 2010-06-30.

Column B containts texts.

I want to counta column for period 2010-07-01 to 2010-07-30.

Is there a formula to do so?
Thanks.

 

[SkipVought]

Hi,

I would put your two date limits in 2 cells. Lets assume that the lower date is in D1 and the higher date is in E1. Lets assume further that your data is in rows 2 thru 999.
[tt]
=sumproduct(--(A2:A999>=D1)*(A2:A999<=E1))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
Thanks for your reply.
I've tried your formula but it returns value 0 which isnt true.

Thanks.

 

[arvarr]

Hi Skip,
Sorry, i tried your formula again and it works like a gem.
It performs the count well.

How does the above sumproduct formula works if you dont mind explaining a bit because I've never used this formula. Its quite useful seeing that i dont have to think of so many if's formula.

Thanks for your help.

Another question regarding this thread.

Lower Date = column D1
Higher Date = column E1

In column F1:F999, it is the number of hours.

What formula can i use to add up all the hours within the lower and higher date range?

Thanks.

 

[SkipVought]

Are your values in column A, ACTUAL dates?

The way that you can determine is to SELECT the column and change the Format to GENERAL. If ALL the cells change their DISPAY values to NUMBERS, then they are REAL dates. UNDO to reset the original format.

Do the same for your two date limits if necessary.

If the date values do NOT CHANGE, then you have TEXT as dates. In most cases you can copy a 1 from an unused cell, SELECT the cells containing your date strings and do Edit > Paste Special -- MULTIPLY

If EVERYTHING contains REAL dates, then there's another problem.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
Yes, they are real dates.

Thanks.

 

[arvarr]

Everything in the column and range limits are actual dates.
Do i need to change them to text or is there a workaround to total the hrs within the date limits?
Thanks.

 

[SkipVought]

I'm trying to do a counta for items in a column.

to total the hrs within the date limits

Which is it?

This formula WORKS! Tested!

Please post a sample of the data you have in column A, starting a A1.

Please COPY the formula as it exists in your cell and post

Please address EACH of the issues raised in this post.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

My sample data
[tt]
Dates
6/26/2010
6/30/2010
7/1/2010
7/2/2010
7/3/2010
7/4/2010
7/28/2010
7/29/2010
7/30/2010
7/31/2010
8/1/2010
8/2/2010
8/3/2010
[/tt]
My criteria
[tt]
D1 E1
7/1/2010 7/30/2010
[/tt]
My formula
[tt]
=SUMPRODUCT(--(A2:A999>=D1)*(A2:A999<=E1))
[/tt]
My result: 7

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
My apologies for the confusion.

What I am trying to do now is to add the hours within the date range limit.

My Criteria (Date Range Limit)
D1 E1
7/1/2010 7/31/2010

My sample data

Dates Hrs
6/26/2010 20
6/30/2010 15
7/01/2010 18
7/02/2010 20
7/03/2010 12
7/04/2010 5
7/28/2010 6
7/29/2010 9
7/30/2010 18
7/31/2010 54
8/01/2010 63
8/02/2010 25

Thanks.

 

[SkipVought]

Assuming that Hrs is in column B...
[tt]
=SUMPRODUCT((A2:A999>=D1)*(A2:A999<=E1)*(B2:B999))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Thanks Skip. Works very well.
Greatly appreciated.