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"

 

[Marcílio_Lobão]

imback2nite, Good evening.

Try to use:

=COUNTIF($B$21:$W$65,A2)  + SUMPRODUCT(($B$21:$W$21="LARGE")*($B$22:$W$65=A2)) & " Classes."

Is that what you want?

I hope it helps.

 

[imback2nite]

SUMPRODUCT?? I didn't even think of that! Thank you so Marcílio! That did the trick. I'm able to use this in another part of my sheet also! Saves me sooo much time!

 

[Marcílio_Lobão]

imback2nite, Good evening.

I'm very glad to have helped you.

Thanks for the feedback.

 

[imback2nite]

For some reason I'm not able to get the SUMPRODUCT to work properly. It will only count a single column at a time. My problem is I have two criteria. The Teachers name and whether 'LARGE' appears at the top of each column. I have 11 columns.

 

[Aladin Akyurek]

Care to post 5 rows from B20:E25 of Galleys as is, A2 of Instructors, and indicate that must obtain for A2 for the posted sample?

 

[Marcílio_Lobão]

imback2nite, Good morning.

Are you having problems with applying the formula using other ranges or in other spreadsheets?

I applied the formula in an example I created for you.

https://www.sendspace.com/file/189xiu

The formula works.
Adapt the intervals to your reality.

Any doubt save your spreadsheet to a free website www.sendspace.com or another and place the download link here.

I'm sure you will be helped.

 

[imback2nite]

I like the sendspace web site! Here's the link to what I'm trying to do.

https://www.sendspace.com/file/ofsy3p

I think I found what may be a problem. At the top of every two columns the word "LARGE" that I need to use in the formula is in a merged cell. Could that be it?

 

[Aladin Akyurek]

Can't we just have what I asked for in post #25 directly here?

 

[Marcílio_Lobão]

imback2nite, Good evening.

I applied the formula on your model.

I did a small modification in the 3 columns where the LARGE one is.

https://www.sendspace.com/file/4wv0gy

Is this modification acceptable for you?

Please, tell us if it worked now.

I hope it helps.