Evaluating multiple ranges for a count function

[JustMe602]

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.

 

[Jerry W. Lewis]

=SUMPRODUCT((MONTH(C2:C40)=MONTH(A1))*((B2:B40="A")+(B2:B40="B"))*(((D240>0)+(E2:E40>0)+(F2:F40>0)+(G2:G40>0))>0))

Jerry

 

[JustMe602]

I am not getting the suggested

SUMPRODUCT function to work?

JustMe

 

[JustMe602]

I am still in need of assistance to this problem.

Any takers?

Thanks

 

[Jerry W. Lewis]

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)=MONTH(A1)))*((B2:B40="A")+(B2:B40="B"))*(((D240>0)+(E2:E40>0)+(F2:F40>0)+(G2:G40>0))>0))
or
=SUM((IF(ISNUMBER(C2:C40),MONTH(C2:C40)=MONTH(A1)))*((B2:B40="A")+(B2:B40="B"))*(((D240>0)+(E2:E40>0)+(F2:F40>0)+(G2:G40>0))>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