Prev: If then between two dates
Next: Protect cell but allow copy command?
From: EXCELably Challenged on 19 Mar 2010 16:31
i need to have a single formula that will count the number of times 4
different conditions are met. I 2 types of members, in 3 different markets.
i need to identify, the member type A, from market A, during January, that is
not duplicated.
i have the date converted to a text field in column B (labeled all january
dates as j110), market in column C, name in column E (last name space first
name) and member type in column H(indicated by an x in field if true). I am
using the following formula and it wont work. I could really use some help.
thanks.

=SUMPRODUCT(--('Total Class Attendance-Sign In'!C3:C20000="aus"),--('Total
Class Attendance-Sign In'!H3:H20000="x"),--('Total Class Attendance-Sign
In'!B3:B20000="j110"),--(('Total Class Attendance-Sign
In'!E3:E20000<>"")/(CountIf('Total Class Attendance-Sign In'!E3:E20000,'Total
Class Attendance-Sign In'!E3:E20000&"")))


From: Fred Smith on 19 Mar 2010 18:46
1. You probably want J110, not "j110"
2. You probably want to remove the &"" after E3:E20000, or explain to us
what it's for.
3. Not sure why you converted dates to text, but it will work as long as
you're comparing text to text, or date to date.

If you need more help, post back with what you mean by "won't work". Error
returned? Zero returned? Wrong value returned? etc.

Finally, my bet is your task would be a lot simpler with a Pivot Table.

Regards,
Fred

"EXCELably Challenged" <EXCELablyChallenged(a)discussions.microsoft.com> wrote
in message news:6C817646-002E-4E64-B2FB-379ABB93B402(a)microsoft.com...
>i need to have a single formula that will count the number of times 4
> different conditions are met. I 2 types of members, in 3 different
> markets.
> i need to identify, the member type A, from market A, during January, that
> is
> not duplicated.
> i have the date converted to a text field in column B (labeled all january
> dates as j110), market in column C, name in column E (last name space
> first
> name) and member type in column H(indicated by an x in field if true). I
> am
> using the following formula and it wont work. I could really use some
> help.
> thanks.
>
> =SUMPRODUCT(--('Total Class Attendance-Sign In'!C3:C20000="aus"),--('Total
> Class Attendance-Sign In'!H3:H20000="x"),--('Total Class Attendance-Sign
> In'!B3:B20000="j110"),--(('Total Class Attendance-Sign
> In'!E3:E20000<>"")/(CountIf('Total Class Attendance-Sign
> In'!E3:E20000,'Total
> Class Attendance-Sign In'!E3:E20000&"")))
>
>

 | 
Pages: 1
Prev: If then between two dates
Next: Protect cell but allow copy command?