# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have an excel sheet with two columns, both are numbered 1-4. They correspond to Pre and Post examination data for a group of students. more studnets took the pre exam than the post exam...but the data is only good if include studnets with BOTH pre and post examination. I am trying to count the # of students who obtained each score (1-4) on their pre examination who also obtained the follow up examination. Can I do this via countifs?
Solved by B. B. in 47 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 26/07/2018 - 02:58
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
User 26/07/2018 - 02:58
thank you
User 26/07/2018 - 02:58
thank you!
User 26/07/2018 - 02:59
thank you
Excelchat Expert 26/07/2018 - 02:59
Nice. Okay, I've read your concern and you wish to count students meeting your criteria. Will it be okay if you can share the file you're working on? :)
Excelchat Expert 26/07/2018 - 03:00
Oh, apologies on that. I was actually deciphering the problem statement you posted. :)
User 26/07/2018 - 03:00
sweet
User 26/07/2018 - 03:00
okay
User 26/07/2018 - 03:00
i cannot share the document due to hippa
User 26/07/2018 - 03:00
unfortunately
Excelchat Expert 26/07/2018 - 03:00
Oh I see. That's alright. Would it be okay then if we use fake names to work on. :)
Excelchat Expert 26/07/2018 - 03:01
I just need to know the how the data is structured, on what columns are the scores included and such.
User 26/07/2018 - 03:01
there are 219 names total
User 26/07/2018 - 03:01
so column AN is pre test
User 26/07/2018 - 03:02
column Bw is post test
User 26/07/2018 - 03:02
can i post in the sheet
User 26/07/2018 - 03:02
?
Excelchat Expert 26/07/2018 - 03:03
Yes you may. Please do :)
User 26/07/2018 - 03:03
this is what the count should look like at the end
User 26/07/2018 - 03:03
so i have more pre than post
User 26/07/2018 - 03:03
70 vs 39
Excelchat Expert 26/07/2018 - 03:04
Okay I see.
User 26/07/2018 - 03:04
and this data comes from roughly half of the patients (they are split up according to identifiers)
User 26/07/2018 - 03:05
so 70/84 were normal
Excelchat Expert 26/07/2018 - 03:05
Okay, noted on that.
User 26/07/2018 - 03:06
and so in the data sheet the columns AN (pre test) and BW( post test) have a single number 1-4 which explains test performance
User 26/07/2018 - 03:07
i did countif ,"1" or "2" etc AN2:AN144 to get the data in that sheet
Excelchat Expert 26/07/2018 - 03:08
Do you mean to say you want to count the number of cells having a 1 or 2 values within the AN2:AN144 range?
User 26/07/2018 - 03:08
no sorry normal=1 pale=2 swollen=3 and other =4
User 26/07/2018 - 03:08
I have summed them from the pre and post data columns
Excelchat Expert 26/07/2018 - 03:08
Okay, I see.
User 26/07/2018 - 03:09
but i need to report the data inclduing only pts with both pre and post data
Excelchat Expert 26/07/2018 - 03:09
Can we extract your requirement from the data you provided? Or will we need to look at as well the data from which these values are coming from?
Excelchat Expert 26/07/2018 - 03:10
If I get this right, you need to report the count that has the same point in both pre and post test. is this correct?
User 26/07/2018 - 03:11
Kind of. I can only report the count if the Initial test, if the student has had a post test
User 26/07/2018 - 03:11
someone on a forum said this
User 26/07/2018 - 03:11
"I have two columns, in first column "Description" a have different categorizations, in second column "Name" i have names and also i have blank cells among them. I want to count all categories from column "Description" with blank cells in column "Name". In another words, i want to count rows that contains any text in column"Description" and blank cells in column "Name" ( rows that meet both criteria) I tryed with COUNTIFS function, but it doesnt working. I signed BLANK cell as "" under function criteria."
User 26/07/2018 - 03:11
my question (i think) is the same
User 26/07/2018 - 03:11
except im looking for FILLED not blank cells in that second column
Excelchat Expert 26/07/2018 - 03:14
Okay I see. So in your case, you want to count all names that has the same score both in the Pre Test and Post Test. So for example, if Name 1 has a score of 1 in both pre and post tests, he is counted. However, if Name2 has a score of 1 in pre test but 2 in post test, he is not counted. Is this correct?
User 26/07/2018 - 03:15
not nessarily the same score. but having completed the test
User 26/07/2018 - 03:15
some imrpove some worsen
Excelchat Expert 26/07/2018 - 03:15
Okay, noted on that. So you just need the count of names who performed both tests, Is this right? :)
User 26/07/2018 - 03:15
YES :) perfect thank you
User 26/07/2018 - 03:16
i need to count them and ideally arrange them in the same format that I have posted here in the doc
User 26/07/2018 - 03:16
except they would have the SAME TOTAL at the bottom, even though their other values may difffer
Excelchat Expert 26/07/2018 - 03:17
Okay, the formula would be =COUNTIFS(AN:AN:,"<>",BW:BW,"<>")
Excelchat Expert 26/07/2018 - 03:17
If AN has the score of pre test and BW has the score of post test, this formula will count all values that has a score on both pre and post test.
User 26/07/2018 - 03:17
that counts everyone that has taken both tests correct?
Excelchat Expert 26/07/2018 - 03:17
Yes, that is correct. :)
User 26/07/2018 - 03:18
than ky ou
Excelchat Expert 26/07/2018 - 03:18
Were you able to test this formula? :)_
User 26/07/2018 - 03:18
tryinog now
Excelchat Expert 26/07/2018 - 03:18
Btw, you need to enter that formula in the same worksheet having the pre and post tests.
User 26/07/2018 - 03:19
right
User 26/07/2018 - 03:20
i copied and put in the domain
User 26/07/2018 - 03:20
do i need the numbers 1-4 in "<>"?
Excelchat Expert 26/07/2018 - 03:20
Not necessarily if you just want to count the number of names that has performed both the pre and post tests.
User 26/07/2018 - 03:21
okay ill try again
User 26/07/2018 - 03:23
didnt work....
User 26/07/2018 - 03:23
but I was able to get a number using "=COUNTIFS(AN144:AN232,">0",BU144:BU232,">=0")
Excelchat Expert 26/07/2018 - 03:24
That as well works. But it only counts all matches from row 144 to row 232.
Excelchat Expert 26/07/2018 - 03:24
Oh wait, sorry.
Excelchat Expert 26/07/2018 - 03:25
=COUNTIFS(AN144:AN232,">0",BU144:BU232,">0")
Excelchat Expert 26/07/2018 - 03:25
I edited your formula to correct the >=0 condition on the second criteria.
User 26/07/2018 - 03:25
it worked
Excelchat Expert 26/07/2018 - 03:26
Nice. The initially provided solution should work as well, just needs to be modified a little to fit the references you're looking intol.
User 26/07/2018 - 03:27
thank you. so before I called you guys I had this in my spreadsheet trying to get a count of everyone with pre and post test data...instead it gave me a single count. similar to the formula you just gave
User 26/07/2018 - 03:28
If i use the formula you just gave this is the data that I'll get.
Excelchat Expert 26/07/2018 - 03:28
Okay I see. The formula however has an incorrect second criteria.
User 26/07/2018 - 03:28
right!
Excelchat Expert 26/07/2018 - 03:28
Nice, glad to be of help. :)
User 26/07/2018 - 03:28
so how do I fix it
Excelchat Expert 26/07/2018 - 03:29
Oh, you could just use this: =COUNTIFS(AN144:AN232,">0",BU144:BU232,">0")
Excelchat Expert 26/07/2018 - 03:31
User 26/07/2018 - 03:31
not quite. bc I need to get the post data
User 26/07/2018 - 03:31
i was able to count who had pre data
Excelchat Expert 26/07/2018 - 03:32
Does column AN contain the pre data and column BU contain the post data?
User 26/07/2018 - 03:32
yes
User 26/07/2018 - 03:32
AN-Right pre; AO; left pre
User 26/07/2018 - 03:32
I just didnt include left and right for simplicity
User 26/07/2018 - 03:33
AN pre- BU-post
Excelchat Expert 26/07/2018 - 03:34
Okay. I thought we're dealing only with AN and BU columns.
User 26/07/2018 - 03:34
my apologies
Excelchat Expert 26/07/2018 - 03:35
Could you tell me exactly what columns you need to consider. AN = Right PRE, AO = Left PRE. How about for Right POST and Left POST?
User 26/07/2018 - 03:35
Bu=right post Bv=left post
Excelchat Expert 26/07/2018 - 03:36
And you want to count the number of names that has data on columns AN, AO, BU and BV. If the name has no value on at least one of the four columns, then that name is not counted. Is this your preferred requirement? :)
User 26/07/2018 - 03:36
yes! thank you
User 26/07/2018 - 03:37
that is it perfect
Excelchat Expert 26/07/2018 - 03:37
Just to clarify, our names are only on rows 144 to 232. Is this correct? :)
User 26/07/2018 - 03:38
yes thank you
Excelchat Expert 26/07/2018 - 03:39
The formula should then be this:
Excelchat Expert 26/07/2018 - 03:39
=COUNTIFS(AN144:AN232,">0",AO144:AO232,">0",BU144:BU232,">0",BV144:BV232,">0")
User 26/07/2018 - 03:39
okaY I wil try it now
Excelchat Expert 26/07/2018 - 03:39
This counts all names that has score on all 4 columns. If a name only has 3 scores out of the 4 columns, it isn't counted.
User 26/07/2018 - 03:41
and to count the number of people with a specific score I just put in "1"
User 26/07/2018 - 03:41
for example
Excelchat Expert 26/07/2018 - 03:42
If you want to count all people having a score of 1 in all 4 columns, this should be the formula:
Excelchat Expert 26/07/2018 - 03:42
=COUNTIFS(AN144:AN232,"=1",AO144:AO232,"=1",BU144:BU232,"=1",BV144:BV232,"=1")
User 26/07/2018 - 03:42
okay i get it! sorry
User 26/07/2018 - 03:43
okay. So I can put together how many people got what.
Excelchat Expert 26/07/2018 - 03:43
Oh, please don't be sorry. If you wish to count all people with 2 as their score on all four columns, just changed the 1 to 2.
Excelchat Expert 26/07/2018 - 03:43
Yes, that is correct. :)
User 26/07/2018 - 03:43
and to find people who improved I can just switch them between AN/0 and BU/V
User 26/07/2018 - 03:44
to count the total I use the original formula you gave and put that at the bottom
Excelchat Expert 26/07/2018 - 03:44
Yes, that is correct. Basically, you just need to play around the reference values to match your conditions. :)
User 26/07/2018 - 03:44
Wow. thank you
Excelchat Expert 26/07/2018 - 03:44
Since you're dealing with numerical values, this would be very doable. :)
User 26/07/2018 - 03:44
yes thank you. This makes sense
Excelchat Expert 26/07/2018 - 03:45
Is there any clarification that I can assist you regarding the solution provided? :)
User 26/07/2018 - 03:45
I dont think so....it makes a lot more sense now
Excelchat Expert 26/07/2018 - 03:45
Nice! You can now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice day! :)
User 26/07/2018 - 03:46
Yes I will! youre the best. thank you thank you very much!!!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.