COUNTIF, not COUNTIFS

[imback2nite]

Thank you for looking. I’m looking for a much smaller formula than the one below. What I’m doing is in multiple columns I’m looking for a name in a grid. The name will appear multiple times in both the same column and other columns. The first countif will count the number of times the teachers name will appear. But, if the work ‘LARGE’ appears at the top of the column the teachers name will be counted again. I’ve been toying with MATCH, INDEX etc. and I’m just not getting it. Thanks for any help you can give! Oh the formula cell is B2 and I’m using Excel 2003 so I can’t use SUMIFS or COUNTIFS, as I understand it.

=COUNTIF(Galleys!$B$21:$W$65,A2)+IF(Galleys!$B$20="LARGE",SUM(COUNTIF(Galleys!C21:C65,Instructors!A2)),0)+IF(Galleys!$D$20="LARGE",SUM(COUNTIF(Galleys!E21:E65,Instructors!A2)),0)+IF(Galleys!$F$20="LARGE",SUM(COUNTIF(Galleys!G21:G65,Instructors!A2)),0)+IF(Galleys!$H$20="LARGE",SUM(COUNTIF(Galleys!I21:I65,Instructors!A2)),0)+IF(Galleys!$J$20="LARGE",SUM(COUNTIF(Galleys!K21:K65,Instructors!A2)),0)+IF(Galleys!$L$20="LARGE",SUM(COUNTIF(Galleys!M21:M65,Instructors!A2)),0)+IF(Galleys!$N$20="LARGE",SUM(COUNTIF(Galleys!O21:O65,Instructors!A2)),0)+IF(Galleys!$P$20="LARGE",SUM(COUNTIF(Galleys!Q21:Q65,Instructors!A2)),0)+IF(Galleys!$R$20="LARGE",SUM(COUNTIF(Galleys!S21:S65,Instructors!A2)),0)+IF(Galleys!$T$20="LARGE",SUM(COUNTIF(Galleys!U21:U65,Instructors!A2)),0)+IF(Galleys!$V$20="LARGE",SUM(COUNTIF(Galleys!W21:W65,Instructors!A2)),0)&" Classes"

 

[imback2nite]

Yes. So it was the merging that fouled things up. My fault. I know merging is a no-no but in this case I thought I needed it. I'll work around that and thank you once again. I apologize for my mistake.

Aladin, how would I post a two page workbook? I'm not sure how I can do that. Thank you for your help.

 

[Aladin Akyurek]

Yes. So it was the merging that fouled things up. My fault. I know merging is a no-no but in this case I thought I needed it. I'll work around that and thank you once again. I apologize for my mistake.

Aladin, how would I post a two page workbook? I'm not sure how I can do that. Thank you for your help.

Not the workbook. Just

5 rows from B20:E25 of Galleys as is, A2 of Instructors, and indicate that must obtain for A2 for the posted sample.​