If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sumproduct with 4 conditions
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&""))) |
#2
|
|||
|
|||
sumproduct with 4 conditions
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" wrote in message ... 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&""))) |
Thread Tools | |
Display Modes | |
|
|