Nested formulas with CSE

[KatK6]

Hi guys,

Its a bit of tricky question but I could really use some help. Basically I'm using a whole bunch of Array Formulas (CSE function) to look up and count cells that are a listed in an adjacent excel sheet.

I need a function that says:
Count the number of people in excel sheet 'survey data' who had the text "male" in K2:K3193 who also met the following criteria...
Have the same string of text in a column of the 'survey data' sheet as is in cell B1 of this sheet
AND who have a value less than or equal to B4 in column BI of the 'survey data' sheet (these values are referring to dates)
AND who have a value of 0 in column DK of the 'survey data' sheet
Only where all 3 conditions are met do I want it to count the number rows who were male.

I've gotten this far but I think the formula is counting anyone who meets one of the three criteria (not just those who meet all three)
=SUM(('surveydata'!$K$2:$K$3193="male")
*(IF((ISERROR((FIND(B$1,'survey data'!$AA$2:$AA$3193)))),0,1))
*IF('survey data'!$BI$2:$BI$3193<=B$4,1,0))
*IF('survey data'!$DK$2:$DK$3193=0,1,0)

Is it a matter of rearranging the brackets, adding some 'AND' commands or swapping to a different formula (SumIFS?).

FYI- I'm running Excel 2011 (Mac).

 

[Dave Patton]

A concise sample would be very useful.
You can add your sheet reference information and edit to hard code the criteria if you prefer.

Try either of

=SUMPRODUCT(--($K$2:$K$3193=C1),--($AA$2:$AA$3193=B1),--($BI$2:$BI$3193<=B$4),--($DK$2:$DK$3193=D1))

=SUM(IF($K$2:$K$3193=C1,IF($AA$2:$AA$3193=B1,IF($BI$2:$BI$3193<=B$4,IF($DK$2:$DK$3193=D1,1,0)))))

The second formula must be array entered.

N.B. I have not used Excel 2011 (Mac).

 

[barry houdini]

Hello KatK6, welcome to MrExcel

COUNTIFS might be your best bet. Try this

=COUNTIFS('surveydata'!$K$2:$K$3193,"male",'survey data'!$AA$2:$AA$3193,B$1,'survey data'!$BI$2:$BI$3193,"<="&B$4,'survey data'!$DK$2:$DK$3193,0)

For the second condition in that formula only exact matches with B1 will count - your use of FIND implies that the B1 value might be found amongst othe text, if that's the case then you can use wildcards, i.e.

=COUNTIFS('surveydata'!$K$2:$K$3193,"male",'survey data'!$AA$2:$AA$3193,"*"&B$1&"*",'survey data'!$BI$2:$BI$3193,"<="&B$4,'survey data'!$DK$2:$DK$3193,0)

 

[KatK6]

Thanks for the fix and the explanation Barry