COUNTIF? COUNTIFS? SUMIF? SUM(IF)[CO...AARRGH SOMEONE PLEASE HELP!

[christopherjpurtell]

I am trying to build a formula to count names that may repeat in multiple columns. I work for a dance studio, and I am tallying up our 5 and 10 year students with the first column being the enrollment from 2012, with each column representing the next consecutive year. I have tried to use =Countif(A2:M186, M2) but it is not producing the correct mathematical sum unless the names happen to be in the same row. I'm going crazy trying to figure this out....please help!

 

[KRice]

How are you defining the "correct mathematical sum". What is in M2? Are you saying that columns A:M are student names for years 2012 through 2024. Are the same names on each row, or might the same names be staggered...found on different rows?

 

[Eric W]

Welcome to the MrExcel forum!

That should work (see below), but you have one issue in that the criterium (M2) is in the range being searched (A2:M186), so your count will be off by at least one.

Book2 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023	2024		Emma
2	Fred	Dan	Amy	Bob	Cyndi	Amy	Bob	Cyndi	Amy	Cyndi	Bob	Dan	Cyndi		15
3	Emma	Fred	Bob	Amy	Amy	Cyndi	Cyndi	Dan	Fred	Emma	Bob	Emma	Fred
4	Fred	Fred	Emma	Gina	Gina	Fred	Amy	Bob	Amy	Gina	Emma	Amy	Bob
5	Dan	Gina	Amy	Cyndi	Dan	Cyndi	Emma	Dan	Gina	Emma	Fred	Fred	Amy
6	Gina	Cyndi	Gina	Amy	Bob	Amy	Emma	Gina	Emma	Gina	Amy	Emma	Bob
7	Dan	Gina	Amy	Emma	Bob	Gina	Fred	Fred	Amy	Cyndi	Dan	Bob	Emma
8	Gina	Bob	Fred	Cyndi	Dan	Gina	Cyndi	Bob	Gina	Amy	Gina	Dan	Fred
9	Cyndi	Emma	Emma	Dan	Bob	Fred	Emma	Gina	Bob	Gina	Bob	Bob	Dan
10	Cyndi	Bob	Gina	Fred	Bob	Amy	Fred	Fred	Dan	Amy	Gina	Dan	Amy
Sheet2

Cell Formulas
Range	Formula
O2	O2	=COUNTIF(A2:M10,O1)

 

[christopherjpurtell]

hmmm im still missing somethings

Student Enrollment Lists 2024.xlsx

 

[christopherjpurtell]

How are you defining the "correct mathematical sum". What is in M2? Are you saying that columns A:M are student names for years 2012 through 2024. Are the same names on each row, or might the same names be staggered...found on different rows?

yes exactly,they are staggered

 

[Asbestos_Jen]

Try this and fill down for each name.

=COUNTIF($B$2:$M$182,O2)

 

[KRice]

Thank you...there are some issues. For example, your data set includes Bluestein ,jesse and Bluestein- ,Jessie in 2020 and 2023, respectively. They won't match...are they supposed to? You may need to do some data cleanup first.

 

[Asbestos_Jen]

I just had a closer look at your data, and noticed that there are some differences in the way names are typed, which will affect your counts. For example, each of the people below have two different spellings entered.

Babini , Scarlet

Babini ,Scarlet

Baldwin , Nia

Baldwin ,Nia

Ball , Josie

Ball ,Josie

 

[KRice]

What do the numbers mean after some of the names...for example, in 2023, Comeau-4? Some data cleanup is needed for the counts to work correctly.

 

[KRice]

I mentioned some data cleanup would be in order. You could do this with VBA, Power Query, or a formula...a formula approach is shown here. I cleared all formatting in columns Q:AF, and then inserted this formula in R2:

=LET(all,B2:M183,n,MAP(all,LAMBDA(k,LET(s,SEQUENCE(LEN(k)),x,IF(k="","",TEXTJOIN("",TRUE,IF(ISERR(MID(k,s,1)+0),MID(k,s,1),""))),SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(x,".",", "),",",", "),"]","")," ,",",")),"-,",",")))), n)

The formula processes your master table using the MAP function to clean up extra spaces, replace periods with commas, eliminate spurious hyphens, eliminate numbers appearing in the text string, and ensure that commas separating names are of the form comma space. This creates a new master list that is nearly appropriate for counting individuals over the years, but there is still one problem. There are still cases where individuals' names are spelled differently. This new table spills across columns R:AC.

Then to address the spelling mismatches, a second formula is used in column AF...paste this in AF2:

=LET(a,TOCOL(R2#),SORT(UNIQUE(FILTER(a,a<>""))))

This formula creates a single list of unique, reformatted names that appear in the spilled table created in the previous step. Look through this list and identify any names that need to be corrected and made consistent. You'll need to search the original table in columns B:M for those names...make the changes in columns B:M to achieve consistency, and then re-review the list in column AF until no more name variations are found.

Once that is done, then the tidied-up and reformatted names appearing in R:AC can be used to perform the counting operation desired. If you want a count of everyone across all years, then the formula in AG...and if you want a count of only those enrolled in 2024, then the formula in AD. But note that the counts will not be correct until the spelling mismatches described in the previous paragraph are resolved.

Student Enrollment Lists 2024_rev.xlsx
	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1		2012	2013	2014	2015	2017	2018	2019	2020	2021	2022	2023	2024	Counts 2024 List		Full list unique names	Counts Full List
2		Adams, Sophia	Adair, Bryn	Alvarez, Liliana	Alvarez, Liliana	Alabiso, Maya	Allen, Kaleigh	Azulay, Isabelle			Adams, Finola	Adams, Isolde	Anderson, Ella	3		Adair, Bryn	1
3				Andrews, Sabrina	Andrews, Karina		Azulay, Isabelle				Anderson, Ella	Anderson, Ella	Astore, Jillian	3		Adams, Finola	1
4					Andrews, Sabrina						Astore, Jillian	Astore, Jillian				Adams, Isolde	1
5																Adams, Sophia	1
6																Alabiso, Maya	1
7		Bell, Madison	Bartholomew, Lily	Banwarth, Ada	Bellabona, Mia	Bellabona, Mia	Babini, Scarlet	Babini, Scarlet	Banano, ava	Bellabona, Mia	Bellabonna, Mia	Ball, Josie	Boswell, Lila	1		Allen, Kaleigh	1
8		Breish, Sydney	Bell, Madison	Bellabona, Mia	Belliveau, Zoe	Belliveau, Zoe	Baldwin, Nia	Baldwin, Nia	Bellabona, mia	Bernstein, Jesse	Bernstein, Jesse	Bernard, Avery	Boucher, Corinne	7		Alvarez, Liliana	2
MASTER

Cell Formulas
Range	Formula
R2:AC183	R2	=LET(all,B2:M183,n,MAP(all,LAMBDA(k,LET(s,SEQUENCE(LEN(k)),x,IF(k="","",TEXTJOIN("",TRUE,IF(ISERR(MID(k,s,1)+0),MID(k,s,1),""))),SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(x,".",", "),",",", "),"]","")," ,",",")),"-,",",")))), n)
AD2:AD183	AD2	=BYROW(TAKE(R2#,,-1),LAMBDA(r,IF(r="","",COUNTIF(R2#,r))))
AF2:AF477	AF2	=LET(a,TOCOL(R2#),SORT(UNIQUE(FILTER(a,a<>""))))
AG2:AG477	AG2	=BYROW(AF2#,LAMBDA(r,COUNTIF(R2#,r)))
Dynamic array formulas.