Evaluating multiple ranges for a count function
 

Code:
--------------------
A B C D E F G
Jan-05
Info Date IF EB IF2 EB2
January
A Jan 01, 2005 1 1 1 1
B Jan 01, 2005 3 4 3 4
A Jan 02, 2005 0 0 0 0
Febraury
B Feb 03, 2005 0 1 0 1
C Feb 03, 2005 - - - -
March
A Mar 04, 2005 3 2 3 2
D Mar 04, 2005 1 0 0 0
April
A Apr 02, 2005 2 2 3 2
C Apr 02, 2005 - - - -
May
F May 20, 2005 1 1 1 1
A May 20, 2005 0 0 0 0
G May 08, 2005 5 0 5 0
June
A Jun 08, 2005 1 0 1 0
--------------------



Okay here is what I need help with.
The number of records that have a month that equals A1 and when the
value in column B is either A or B and when there is a value greater
than 0 in any of the columns D through G

Please let me know if you need further clarrification.


--
JustMe602
------------------------------------------------------------------------
JustMe602's Profile: http://www.excelforum.com/member.php...o&userid=27854
View this thread: http://www.excelforum.com/showthread...hreadid=473641

=SUMPRODUCT((MONTH(C2:C40)=MONTH(A1))*((B2:B40="A" )+(B2:B40="B"))*(((D2:D400)+(E2:E400)+(F2:F400) +(G2:G400))0))

Jerry

JustMe602 wrote:

Code:
--------------------
A B C D E F G
Jan-05
Info Date IF EB IF2 EB2
January
A Jan 01, 2005 1 1 1 1
B Jan 01, 2005 3 4 3 4
A Jan 02, 2005 0 0 0 0
Febraury
B Feb 03, 2005 0 1 0 1
C Feb 03, 2005 - - - -
March
A Mar 04, 2005 3 2 3 2
D Mar 04, 2005 1 0 0 0
April
A Apr 02, 2005 2 2 3 2
C Apr 02, 2005 - - - -
May
F May 20, 2005 1 1 1 1
A May 20, 2005 0 0 0 0
G May 08, 2005 5 0 5 0
June
A Jun 08, 2005 1 0 1 0
--------------------



Okay here is what I need help with.
The number of records that have a month that equals A1 and when the
value in column B is either A or B and when there is a value greater
than 0 in any of the columns D through G

Please let me know if you need further clarrification.

I am not getting the suggested

SUMPRODUCT function to work?

JustMe.


--
JustMe602
------------------------------------------------------------------------
JustMe602's Profile: http://www.excelforum.com/member.php...o&userid=27854
View this thread: http://www.excelforum.com/showthread...hreadid=473641

I am still in need of assistance to this problem.

Any takers?

Thanks.


--
JustMe602
------------------------------------------------------------------------
JustMe602's Profile: http://www.excelforum.com/member.php...o&userid=27854
View this thread: http://www.excelforum.com/showthread...hreadid=473641

The MONTH function produces an error with non-numeric cells that poisons
the calculation. Expand to either

=SUMPRODUCT((IF(ISNUMBER(C2:C40),MONTH(C2:C40)=MON TH(A1)))*((B2:B40="A")+(B2:B40="B"))*(((D2:D400)+ (E2:E400)+(F2:F400)+(G2:G400))0))
or
=SUM((IF(ISNUMBER(C2:C40),MONTH(C2:C40)=MONTH(A1)) )*((B2:B40="A")+(B2:B40="B"))*(((D2:D400)+(E2:E40 0)+(F2:F400)+(G2:G400))0))

Either must be array entered (Ctrl-Shift-Enter). SUMPRODUCT usually
does not require array entry, but it does in this case becase of the
MONTH function.

Jerry

JustMe602 wrote:

I am not getting the suggested

SUMPRODUCT function to work?

JustMe.