Hi
Excel Office 2003
Summary Page is Worksheet 1, Worksheet 2 contains data for year 2010,
Worksheet 3 contains data for year 2011, etc. In each data worksheet, Column
P contains dates (column is formatted as dates 01/01/10), Column M contains
either "Yes" or "No" and Column G contains either "Active" or "Inactive" On
the summary page, I need to count the number of rows that contain "Yes" in
Column M and "Inactive" in Column G for the 2nd quarter of year 2010. I have
tried several SumProduct formulas I've seen here to no avail. Not sure what
I am doing wrong.

G M P
Inactive Yes 04/04/10

Should count as 1 for 2nd quarter of 2010 but using either formula I've seen
here keeps counting as 1 for 1st quarter of 2010.

Perhaps something like this:
=SUMPRODUCT(--('Sheet 2'!M2:M100="Yes"),--('Sheet
2'!G2:G100="Inactive"),--(MONTH('Sheet 2'!P2:P100)=4),--(MONTH('Sheet
2'!P2:P100)<=6))

--
Best Regards,

Luke M
"jh" wrote in message
...
Hi
Excel Office 2003
Summary Page is Worksheet 1, Worksheet 2 contains data for year 2010,
Worksheet 3 contains data for year 2011, etc. In each data worksheet,
Column
P contains dates (column is formatted as dates 01/01/10), Column M
contains
either "Yes" or "No" and Column G contains either "Active" or "Inactive"
On
the summary page, I need to count the number of rows that contain "Yes" in
Column M and "Inactive" in Column G for the 2nd quarter of year 2010. I
have
tried several SumProduct formulas I've seen here to no avail. Not sure
what
I am doing wrong.

G M P
Inactive Yes 04/04/10

Should count as 1 for 2nd quarter of 2010 but using either formula I've
seen
here keeps counting as 1 for 1st quarter of 2010.

Sumproduct will work but with multiple sheets the formulas will get long. You
will need =Sumproduct(2010sheet) + sumproduct(2011sheet)

Here is a link to sumproduct explained...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

You could also look at a pivot table with multiple consolidation ranges.
--
HTH...

Jim Thomlinson


"jh" wrote:

Hi
Excel Office 2003
Summary Page is Worksheet 1, Worksheet 2 contains data for year 2010,
Worksheet 3 contains data for year 2011, etc. In each data worksheet, Column
P contains dates (column is formatted as dates 01/01/10), Column M contains
either "Yes" or "No" and Column G contains either "Active" or "Inactive" On
the summary page, I need to count the number of rows that contain "Yes" in
Column M and "Inactive" in Column G for the 2nd quarter of year 2010. I have
tried several SumProduct formulas I've seen here to no avail. Not sure what
I am doing wrong.

G M P
Inactive Yes 04/04/10

Should count as 1 for 2nd quarter of 2010 but using either formula I've seen
here keeps counting as 1 for 1st quarter of 2010.

I might add that when I have SUMPRODUCT formulas with date criteria, I
typically enter my date criteria somewhere on the worksheet (outside the
print area) and then point to that fixed cell when I get to the part of the
formula saying (Range:Range=startdate)*(Range:Range<=enddate). To me it's
easier and more reliable.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Jim Thomlinson" wrote:

Sumproduct will work but with multiple sheets the formulas will get long. You
will need =Sumproduct(2010sheet) + sumproduct(2011sheet)

Here is a link to sumproduct explained...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

You could also look at a pivot table with multiple consolidation ranges.
--
HTH...

Jim Thomlinson


"jh" wrote:

Hi
Excel Office 2003
Summary Page is Worksheet 1, Worksheet 2 contains data for year 2010,
Worksheet 3 contains data for year 2011, etc. In each data worksheet, Column
P contains dates (column is formatted as dates 01/01/10), Column M contains
either "Yes" or "No" and Column G contains either "Active" or "Inactive" On
the summary page, I need to count the number of rows that contain "Yes" in
Column M and "Inactive" in Column G for the 2nd quarter of year 2010. I have
tried several SumProduct formulas I've seen here to no avail. Not sure what
I am doing wrong.

G M P
Inactive Yes 04/04/10

Should count as 1 for 2nd quarter of 2010 but using either formula I've seen
here keeps counting as 1 for 1st quarter of 2010.