Formula Help to find x between date range

[TeresaMarx]

Hi I need some help figuring out a formula for the following

I need to a report that will show True if A:A=Amina and B:B is on or between 01/01/2011 and 01/31/2011

 

[Norie]

This will count the no of rows where column A="Amina" and B has a date in 2011.

=SUMPRODUCT(--(A1:A100="Amina"),--(YEAR(B1:B100)=2011))

Not sure if that's what you want though.

 

[TeresaMarx]

It looks like that will give me a year as the response, I'm trying to "count" How many items are assigned to a person a month. So I need the formula to look at every thing in Column A (A:A) and pick out all the ones that say Amina, then I need it to look at Column B (B:B) and only count (say True) for the items that were assigned on and between January 1 and January 31.

 

[Norie]

No it will give you the count you describe.

The first part (A1:A100="Amina") checks for 'Amina' in column A.

(YEAR(B1:B100)=2011) checks the date in column B to see if it's in the year 2011 (1 January 2011 - 31 January 2011).

 

[vaskov17]

=SUMPRODUCT(--(A:A="Amina"),--(B:B>=VALUE("1/1/2011")),--(B:B<=VALUE("1/31/2011")))

 

[Aladin Akyurek]

Hi I need some help figuring out a formula for the following

I need to a report that will show True if A:A=Amina and B:B is on or between 01/01/2011 and 01/31/2011

On Excel 2007 or later...

=COUNTIFS(A:A,"Amina",B:B,">=01/01/2011",B:B,"<=01/31/2011")

If you assign cells to criteria like this:

E2: Amina
F2: 01/01/2011
G2: 01/31/2011

H2:

=COUNTIFS(A:A,E2,B:B,F2,B:B,G2)

Or...

=SUMPRODUCT(--($A$2:$A$400=E2),--($B$2:$B$400>=F2),--($B$2:$B$400<=G2))

 

[T. Valko]

Hi I need some help figuring out a formula for the following

I need to a report that will show True if A:A=Amina and B:B is on or between 01/01/2011 and 01/31/2011

One way...

=SUMPRODUCT(--(A2:A10="Amina"),--(TEXT(B2:B10,"mmmyyyy")="Jan2011"))