Countifs for multiple conditions

**batjl9** · 03-25-2011, 04:10 PM

Hi all

I have attached an excel sheet with two questions to set up a countifs formula for multiple conditions. I have tried to explain it fully in the attachment if anyone would be kind enough to look at it.

Thanks
Gemma

**daddylonglegs** · 03-25-2011, 04:24 PM

Hello Gemma,

try this formula in B3 copied down and across

=IFERROR(SUM(COUNTIFS($AA$2:$AA$1002,$A3,$AB$2:$AB$1002,B$2,$AK$2:$AK$1002,{1,2,3,4}))/COUNTIFS($AA$2:$AA$1002,$A3,$AB$2:$AB$1002,B$2),"")

formatted as percentage

For P3 copied down you can use SUMIFS, i.e.

=SUMIF(AA$2:AA$1002,I3,AO$2:AO$1002)

see attached

**batjl9** · 03-25-2011, 04:41 PM

Thank you so much. I have tried to work it out myself forthe last few hours, and within 15 minutes of posting here i get my answer.

thanks again
Gemma

**MarvinP** · 03-25-2011, 04:42 PM

Hi,

Pivot Tables are the answer. Find two examples using your data. I'm not sure if they give exactly what you are asking but I think the could with a little trial and error.

**daddylonglegs** · 03-25-2011, 09:07 PM

...just in case you might be interested.....you could also use a slightly shorter formula for the first one, an "array formula" using AVERAGE function, i.e.

=IFERROR(AVERAGE(IF(($AA$2:$AA$1002=$A3)*($AB$2:$AB$1002=B$2), IF(ISNUMBER(MATCH($AK$2:$AK$1002,{1,2,3,4},0)),1,0))),"")

confirmed with CTRL+SHIFT+ENTER