Need to add to a SUMPRODUCT formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karen - 18 Jun 2007 23:02 GMT

I have the following formula that I need to add a step too:
=SUMPRODUCT(--('Enrollment Log'!$F$3:$F$547<>""),--(MONTH('Enrollment
Log'!$J$3:$J$547)=6),--(YEAR('Enrollment Log'!$J$3:$J$547)=2007))

I need it to look at column F to be not blank OR column C to =EU then add if
the month and year match 6/2007.

If there is a more simple way to approach this other than adding to the
above formula, that would be even better.

Thanks - Karen

Reply to this Message

Bob Phillips - 18 Jun 2007 23:14 GMT

=SUMPRODUCT(--(('Enrollment Log'!$F$3:$F$547<>"")+('Enrollment
Log'!$C$3:$C$547="EU")),
--(MONTH('Enrollment Log'!$J$3:$J$547)=6),
--(YEAR('Enrollment Log'!$J$3:$J$547)=2007))

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have the following formula that I need to add a step too:
> =SUMPRODUCT(--('Enrollment Log'!$F$3:$F$547<>""),--(MONTH('Enrollment
[quoted text clipped - 8 lines]
>
> Thanks - Karen

Reply to this Message

Bernard Liengme - 18 Jun 2007 23:16 GMT

Try
=SUMPRODUCT(('Enrollment Log'!$F$3:$F$547<>")+('Enrollment
Log'!$CF$3:$C$547="EU"),--(MONTH('Enrollment
Log'!$J$3:$J$547)=6),--(YEAR('Enrollment Log'!$J$3:$J$547)=2007))

We don not need the -- in first term since the + operator will convert
Boolean to numeric.

You can often use + for OR, or * for AND in formulas
Example: (TRUE, TRUE, FALSE)+(FALSE,TRUE,FALSE) becomes (1,1,0)
While (TRUE, TRUE, FALSE)*(FALSE,TRUE,FALSE) becomes (0,1,0)
best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

>I have the following formula that I need to add a step too:
> =SUMPRODUCT(--('Enrollment Log'!$F$3:$F$547<>""),--(MONTH('Enrollment
[quoted text clipped - 8 lines]
>
> Thanks - Karen

Reply to this Message