**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.*