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
Was this able to address your concern?
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.
