sumproduct with 4 conditions

  • Thread starter EXCELably Challenged
  • Start date

EXCELably Challenged

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.

=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&"")))

Fred Smith

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.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question