how to compact if function

Question

i have report sheet and data sheet. at data sheet i have 31 col and 2 condition the management team and her visibility (Present or absent or mission )at the report sheet i put this furmula for calculating the number of prenet person and .. and i used below formula can i compact this function?

I need your help.( With special Thanks.)

=IF($C$4=Data!$AA$1,COUNTIF(Data!$AA$2:$AA$3,"=ح"),0)+IF($C$4=Data!$AB$1,COUNTIF(Data!$AB$2:$AB$3,"=ح"),0)+IF($C$4=Data!$AC$1,COUNTIF(Data!$AC$2:$AC$3,"=ح"),0)+IF($C$4=Data!$AD$1,COUNTIF(Data!$AD$2:$AD$3,"=ح"),0)+IF($C$4=Data!$AE$1,COUNTIF(Data!$AE$2:$AE$3,"=ح"),0)+IF($C$4=Data!$AF$1,COUNTIF(Data!$AF$2:$AF$3,"=ح"),0)+IF($C$4=Data!$AG$1,COUNTIF(Data!$AG$2:$AG$3,"=ح"),0)+IF($C$4=Data!$AH$1,COUNTIF(Data!$AH$2:$AH$3,"=ح"),0)+IF($C$4=Data!$AI$1,COUNTIF(Data!$AI$2:$AI$3,"=ح"),0)+IF($C$4=Data!$AJ$1,COUNTIF(Data!$AJ$2:$AJ$3,"=ح"),0)+IF($C$4=Data!$AK$1,COUNTIF(Data!$AK$2:$AK$3,"=ح"),0)+IF($C$4=Data!$AL$1,COUNTIF(Data!$AL$2:$AL$3,"=ح"),0)+IF($C$4=Data!$AM$1,COUNTIF(Data!$AM$2:$AM$3,"=ح"),0)+IF($C$4=Data!$AN$1,COUNTIF(Data!$AN$2:$AN$3,"=ح"),0)+IF($C$4=Data!$AO$1,COUNTIF(Data!$AO$2:$AO$3,"=ح"),0)+IF($C$4=Data!$AP$1,COUNTIF(Data!$AP$2:$AP$3,"=ح"),0)+IF($C$4=Data!$AQ$1,COUNTIF(Data!$AQ$2:$AQ$3,"=ح"),0)+IF($C$4=Data!$AR$1,COUNTIF(Data!$AR$2:$AR$3,"=ح"),0)+IF($C$4=Data!$AS$1,COUNTIF(Data!$AS$2:$AS$3,"=ح"),0)+IF($C$4=Data!$AT$1,COUNTIF(Data!$AT$2:$AT$3,"=ح"),0)+IF($C$4=Data!$AU$1,COUNTIF(Data!$AU$2:$AU$3,"=ح"),0)+IF($C$4=Data!$AV$1,COUNTIF(Data!$AV$2:$AV$3,"=ح"),0)+IF($C$4=Data!$AW$1,COUNTIF(Data!$AW$2:$AW$3,"=ح"),0)+IF($C$4=Data!$AX$1,COUNTIF(Data!$AX$2:$AX$3,"=ح"),0)+IF($C$4=Data!$AY$1,COUNTIF(Data!$AY$2:$AY$3,"=ح"),0)+IF($C$4=Data!$AZ$1,COUNTIF(Data!$AZ$2:$AZ$3,"=ح"),0)+IF($C$4=Data!$BA$1,COUNTIF(Data!$BA$2:$BA$3,"=ح"),0)+IF($C$4=Data!$BB$1,COUNTIF(Data!$BB$2:$BB$3,"=ح"),0)+IF($C$4=Data!$BC$1,COUNTIF(Data!$BC$2:$BC$3,"=ح"),0)+IF($C$4=Data!$BD$1,COUNTIF(Data!$BD$2:$BD$3,"=ح"),0)+IF($C$4=Data!$BE$1,COUNTIF(Data!$BE$2:$BE$3,"=ح"),0)

ح means Present .this is administration Report for 31 day.

Answer 1

Dear Mike

Hi.

Very very very thanks for solving my problem dear. my problem was solved with your solution.

Thanks a lot again Dear.

Best Regards.

M.Rahmani

You're welcome and thanks for the feedback. You might also like to mark my response as answer.

Answer 2

Hi,

I have no idea what that symbol is but this seems to emulate your rather lengthy formula

=SUMPRODUCT((Data!AA1:BE1=C4)*(Data!AA2:BE3="ح"))

Answer 3

Dear Mike

Hi.

Very very very thanks for solving my problem dear. my problem was solved with your solution.

Thanks a lot again Dear.

Best Regards.

M.Rahmani